update - 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

update

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-11-03 : 13:31:21

I have a query like this:
update tbl_product
set product_price = (SELECT a.cost
FROM dbo.nowFinal a INNER JOIN
dbo.tbl_product b ON a.SKU = b.product_oem
WHERE b.product_current_price = b.product_price)
gives me this 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.

what should I do?
thanks

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-11-03 : 14:00:00

The subquery is returning more than one row.
Add additional conditions so that it returns only one row.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-11-03 : 14:00:58

BAD Idea but if nothing else


update tbl_product
set product_price = (SELECT top 1 a.cost
FROM dbo.nowFinal a INNER JOIN
dbo.tbl_product b ON a.SKU = b.product_oem
WHERE b.product_current_price = b.product_price)

OR 

update tbl_product
set product_price = (SELECT MAX(a.cost)
FROM dbo.nowFinal a INNER JOIN
dbo.tbl_product b ON a.SKU = b.product_oem
WHERE b.product_current_price = b.product_price)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 00:38:01

quote:
Originally posted by a.ashabi

I have a query like this:
update tbl_product
set product_price = (SELECT a.cost
FROM dbo.nowFinal a INNER JOIN
dbo.tbl_product b ON a.SKU = b.product_oem
WHERE b.product_current_price = b.product_price)
gives me this 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.

what should I do?
thanks

as per your requirement what should you be updating to the product_price column? sum of all the costs?as the subquery returns more than 1 column, i think you need some aggregation to happen before update

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-11-04 : 02:49:22

a.ashabi: can you post some sample data?
I wouldn't update prices based on sum/max/top 1/distinct values :)

otherwise before doing update try to do select statement just to so see which value/row is problematic and you will examine problem faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources