0

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 ?

raspi surya
  • 109
  • 4
  • 15

3 Answers3

1

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';
Gauravsa
  • 6,330
  • 2
  • 21
  • 30
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'
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
0

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360