Update statement with joins in Oracle

Question

I need to update one column in table A with the result of a multiplication of one field from table A with one field from table B. It would be pretty simple to do this in T-SQL, but I can't write the correct syntax in Oracle.

What I've tried:

UPDATE TABLE_A SET TABLE_A.COLUMN_TO_UPDATE = (select TABLE_A.COLUMN_WITH_SOME_VALUE * TABLE_B.COLUMN_WITH_PERCENTAGE from TABLE_A INNER JOIN TABLE_B ON TABLE_A.PRODUCT_ID = TABLE_B.PRODUCT_ID AND TABLE_A.SALES_CHANNEL_ID = TABLE_B.SALES_CHANNEL_ID) WHERE TABLE_A.MONTH_ID IN (201601, 201602, 201603);

But I keep getting errors. Could anybody help me, please?

Possible duplicate of [Update statement with inner join on Oracle](https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle) --> LOOK for the answer by Morten Anderson. You should google before asking — T.S., Aug 01 '17 at 20:55
This is pretty simple to do in Oracle, too. https://stackoverflow.com/documentation/oracle/8061/update-with-joins#t=201708020102597068665 — , Aug 02 '17 at 01:03

score 1 · Answer 1 · answered Aug 02 '17 at 06:22

I generally prefer to use the below format for such cases since this will ensure there's no update performed if there's no data in the table(query extracted temp table) whereas in the above solution provided by Brian Leach will update the new value as null if there's no record present in the 2nd table but exists in the first table.

UPDATE 
(
    select TABLE_A.COLUMN_TO_UPDATE 
    , TABLE_A.PRODUCT_ID
    , TABLE_A.COLUMN_WITH_SOME_VALUE * TABLE_B.COLUMN_WITH_PERCENTAGE as value
    from TABLE_A 
    INNER JOIN TABLE_B 
    ON TABLE_A.PRODUCT_ID = TABLE_B.PRODUCT_ID 
    AND TABLE_A.SALES_CHANNEL_ID = TABLE_B.SALES_CHANNEL_ID
    AND TABLE_A.MONTH_ID IN (201601, 201602, 201603)
) DATA
SET DATA.COLUMN_TO_UPDATE = DATA.value;

This solution can cause key preserved value issues which shouldn't be an issue here since i expect a single row in both the tables for one product(ID).

More on Key Preserved table concept in inner join can be found here https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:548422757486

I like your solution over mine as it seems straightforward to me, I have upvoted your solution. Although I have been working with Oracle for 10+ years, I wasn't familiar with this form of the update statement. It has been added to my toolbox. Thanks. — Brian Leach, Aug 02 '17 at 17:56

Brian Leach · Answer 2 · 2017-08-02T17:53:46.793

@Jayesh Mulwani raiesed a valid point, this will set the value to null if there is no matching record. This may or may not be the desired result. If it isn't, and no change is desirect, you can change the select statement to:

coalesce((SELECT table_b.column_with_percentage
                                             FROM table_b
                                            WHERE table_a.product_id = table_b.product_id AND table_a.sales_channel_id = table_b.sales_channel_id),1)

If this is the desired outcome, Jayesh's solution will be more efficient as it will only update matching records.

UPDATE table_a
   SET table_a.column_to_update   =   table_a.column_with_some_value
                                    * (SELECT table_b.column_with_percentage
                                         FROM table_b
                                        WHERE table_a.product_id = table_b.product_id
                                          AND table_a.sales_channel_id = table_b.sales_channel_id)
 WHERE table_a.month_id IN (201601, 201602, 201603);

Update statement with joins in Oracle

2 Answers2