Update multiple rows in single select

Question

I'm getting multiple rows in single select error with this SQL:

UPDATE ITEMS IT  
SET IT.BCOST = (SELECT V.PRICE + (V.PRICE * 0.1) 
                FROM VENDOR V
                WHERE V.ITEMNO = IT.ITEMNO)
WHERE EXISTS (SELECT 1 FROM VENDOR V WHERE V.ITEMNO = IT.ITEMNO)

What am I doing wrong?

Answer 1

The select error that you are getting would be from this subquery in your original post:

SELECT V.PRICE + (V.PRICE * 0.1) FROM VENDOR V
WHERE V.ITEMNO=IT.ITEMNO

From what I gather, ITEMNO is a primary key in the ITEMS table, but it is a foreign key reference in the VENDOR table. The error indicates that the statement above it matching more than one entry in the VENDOR table as you have discovered from your last comment. Once that is fixed and the subquery no longer returns more than one row, your original query should work correctly. However, I would recommend considering a different approach to that as it will trigger a similar failure if the subquery ever returns no rows or more than one row. Without knowing more about what you are trying to achieve, I'm not sure what to recommend for that.