Updating a table row multiple values oracle 11g

Question

I m struggling to update one column for a table with a sub query. I have a table where currently one of the values is null. Currently I have:

UPDATE DW1_PURCHASES SET DW1_PURCHASES.TOTAL_AMT = 
(
SELECT DW1_PURCHASES.QUANTITY * DW1_PRODUCTS.PRICE 
FROM DW1_PURCHASES, DW1_PRODUCTS 
WHERE DW1_PURCHASES.PRODUCT_ID =  DW1_PRODUCTS.PRODUCT_ID
)

Although subquery returns data which I need to insert I get a error of single row subquery returns multiple rows.How do I basically shift subquery result to the table?

Thanks.

Pரதீப் · Accepted Answer · 2016-03-13 07:34:35Z

1

You don't have to JOINthe update table inside the sub-query. Just correlate the sub-query with update table

UPDATE DW1_PURCHASES 
SET DW1_PURCHASES.TOTAL_AMT = 
(
SELECT DW1_PURCHASES.QUANTITY * DW1_PRODUCTS.PRICE 
FROM  DW1_PRODUCTS 
WHERE DW1_PURCHASES.PRODUCT_ID =  DW1_PRODUCTS.PRODUCT_ID
)

Note : If your DW1_PRODUCTS table has duplicated PRODUCT_ID then even now there is a possibility to get the same error

answered Mar 13, 2016 at 7:34

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating a table row multiple values oracle 11g

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related