simple SQL query but no idea on how to solve it

Question

hey everyone I have two different tables:

PS(size(primary key), price) Sales(....size,quantity, total_Price)

I tried to execute this statement:

 update Sales
                set total_Price = (select price from PS, Sales 
where Sales.size = PS.size )*Quantity;

but i get this error always

SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.

I know that the error is because the sub query i used in the select statement returns more than one row, that's why i can't work. Can someone please help me solve this problem.

A particular size can have multiple prices? What determines which of multiple prices will be used in this case? — showdev, Aug 19 '14 at 19:02
You need where clause in outer query as well in case you are updating particular record. — kayra, Aug 19 '14 at 19:03
A suggestion:-Dont use Comma seperate joins. Its been ages they have been deprecated! — Rahul Tripathi, Aug 19 '14 at 19:03
You might like UPDATE..FROM instead: http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — Slippery Pete, Aug 19 '14 at 19:05
Heh, nobody ever guesses DB2. editted question to change tags (database is a generic tag, I removed it) — Twelfth, Aug 19 '14 at 19:09
@Twelfth sorry for that.. anyways, can you help me with this? — Alladin, Aug 19 '14 at 19:12
I think that you are looking for something like [this answer](http://stackoverflow.com/a/20031177/2589202). Here's [another doing joins on an update](http://www.toadworld.com/platforms/ibmdb2/w/wiki/6738.update-and-delete-with-subselects.aspx) — crthompson, Aug 19 '14 at 19:13

score 3 · Accepted Answer · answered Aug 19 '14 at 19:20

3

Why are you joining in another sales table? My guess is that:

update Sales s
    set total_Price = (select ps.price * s.Quantity
                       from PS 
                       where s.size = PS.size);

will do what you want. Since ps.size is unique you should not get a SQL0811N

answered Aug 19 '14 at 19:20

Lennart - Slava Ukraini

6,936
1
20
32

thank you very much.. this is what i exactly want, appreciate your help – Alladin Aug 19 '14 at 19:26

bwperrin · Answer 2 · 2014-08-19T19:59:19.143

-1

For starters you can:

UPDATE sales SET total_Price = PS.price * Quantity
from PS, Sales where Sales.size = PS.size

May not be a great idea - if there's more than one matching value it'll pick an arbitrary winner (and Lennart's answer would raise an error, which in fact may be best here). If size is 1-to-1 then this is equivalent to Lennart's answer.

edited Aug 19 '14 at 19:59

answered Aug 19 '14 at 19:14

bwperrin

680
5
12

simple SQL query but no idea on how to solve it

2 Answers2