Oracle Query Update From

Question

I wonder if I can do this query in Oracle database?

UPDATE
    Table_A SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2 FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id WHERE
    Table_A.col3 = 'cool'

this is working on sql server (microsoft). but cant work in oracle db. could you please tell me the reason ?

The reason is - "Oracle developers didn't implement the feature in this way". — Dmitriy, Dec 20 '18 at 09:44

score 1 · Answer 1 · answered Dec 20 '18 at 02:37

You can simply do this:

UPDATE table_a SET table_a.col1 = (SELECT table_B.COl1
                                  FROM table_B 
                                  WHERE table_a.id = table_b.id),
                   table_a.col2 = (SELECT table_B.COl2
                                  FROM table_B 
                                  WHERE table_a.id = table_b.id)
WHERE table_a.col3='cool';

score 1 · Accepted Answer · answered Dec 20 '18 at 02:55

1

You may use merge into in Oracle.

MERGE Into Table_A t USING Table_B s
           ON (t.id = s.id)
        when matched then UPDATE SET
    t.col1 = s.col1, t.col2 = s.col2   
       WHERE t.col3 = 'cool'

answered Dec 20 '18 at 02:55

Kaushik Nayak

30,772
5
32
45

score 0 · Answer 3 · answered Dec 20 '18 at 02:33

Here is one approach which might work:

UPDATE 
(
    SELECT a.col1 AS col1a, a.col2 AS col2a, b.col1 AS col1b, b.col2 AS col2b
    FROM Some_Table a
    INNER JOIN Other_Table b
        ON a.id = b.id
    WHERE a.col3 = 'cool'
) t
SET
    a.col1a = b.col1b,
    a.col2a = b.col2b;

If Oracle doesn't want to run the above, then you will have to use correlated subqueries.

Oracle Query Update From

3 Answers3