Subquery problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subquery problem

Author

Topic

jfloan
Starting Member

4 Posts

Posted - 2008-07-11 : 14:18:10

Hello, I am fairly new to SQL Server. I am currently trying to update a set of records using a query. My code is:

UPDATE pm
SET date_updated = GETDATE()
WHERE (pm_prod_id = (SELECT pm_prod_id
FROM pm AS pm_1
WHERE(pm_color_code = '1233') AND (pm_fragrance_id = '1589')))

There error I am receiving is:
"SQL Execution error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression. The statement has been terminated."

I know my subquery is returning multiple records which is triggering the error. Any ideas how this query should be structured so SQL Server doesn't blow up on me? Thank you in advance!

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-11 : 14:24:50

Try this:

UPDATE pm1
SET date_updated = GETDATE()
FROM pm pm1 INNER JOIN pm pm2 ON pm1.pm_prod_id = pm2.pm_prod_id AND pm2.pm_color_code = '1233' AND pm2.pm_fragrance_id = '1589'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-11 : 14:30:51

[code]UPDATE x
SET x.date_updated = GETDATE()
FROM pm AS x
INNER JOIN (
SELECT pm_prod_id
FROM pm
WHERE pm_color_code = '1233'
AND pm_fragrance_id = '1589'
) AS y ON y.pm_prod_id = x.pm_prod_id[/code]

E 12°55'05.25"
N 56°04'39.16"

jfloan
Starting Member

4 Posts

Posted - 2008-07-11 : 14:38:33

quote:

Originally posted by Peso

UPDATE		x
SET		x.date_updated = GETDATE()
FROM		pm AS x
INNER JOIN	(
			SELECT	pm_prod_id
			FROM	pm
			WHERE	pm_color_code = '1233'
				AND pm_fragrance_id = '1589'
		) AS y ON y.pm_prod_id = x.pm_prod_id

E 12°55'05.25"
N 56°04'39.16"

This worked out perfect. Thank you for saving me a few hours of headache and grief!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources