| sql problem [message #56168] |
Mon, 10 March 2003 17:29  |
tt
Messages: 16
Registered: September 2000
|
Junior Member |
|
|
can t get this simple sql to work:
UPDATE PRODUCT_T a SET INVENTORY=
(select a.inventory-b.quantity from product_t a,INVENTORY_UPDATE_V b where a.miva_code=b.miva_code)
WHERE EXISTS
(SELECT a.inventory-b.quantity FROM product_t a,INVENTORY_UPDATE_V b where a.miva_code=b.miva_code);
it keeps saying 'single query returns more than one row'...
everything looks good, my select comes with the right rows, the miva_codes match, i m gonna go nuts here, any suggestions on what can be a problem?
|
|
|
|
| Re: sql problem [message #56170 is a reply to message #56168] |
Mon, 10 March 2003 19:19  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
This needs to be a correlated subquery - note the absence of a direct reference to the base table (being updated) in the subquery. We just refer to its alias in the outer query:
update product_t a
set inventory = (select a.inventory - b.quantity
from inventory_update_v b
where b.miva_code = a.miva_code)
where exists (select null
from inventory_update_v b
where b.miva_code = a.miva_code);
Of course, there can only be a maximum of one matching miva_code in the inventory_update_v table for a particular miva_code in the base table.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
