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.

Answers


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


Need Your Help

How to get the current date format of Android emulator

java android date date-format

I want to get the current Date Format of Android emulator. Can anyone help me?

Change Class with javascript

javascript html css

I have a site using a body background image. When the browser is resized, I want to change the body class.