CODEWITHSUNDEEP
sqloracle-databasesql-update
Artur Akhmetgareev
Artur Akhmetgareev

Reputation: 23

Updating a table row multiple values oracle 11g

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.

Upvotes: 1

Views: 360

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 1

Related Questions