Update statement with inner join on Oracle

Problem

I have a MySQL query that runs perfectly, however when I execute it on Oracle, I get the following error:

The query is:

UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';

Asked by user169743

Solution #1

In Oracle, that syntax is invalid. This is what you can do:

UPDATE table1 SET table1.value = (SELECT table2.CODE
                                  FROM table2 
                                  WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
            FROM table2 
            WHERE table1.value = table2.DESC);

Alternatively, you might be able to perform the following:

UPDATE 
(SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW

It depends if the inline view is considered updateable by Oracle ( To be updatable for the second statement depends on some rules listed here ).

Answered by Tony Andrews

Solution #2

Use this:

MERGE
INTO    table1 trg
USING   (
        SELECT  t1.rowid AS rid, t2.code
        FROM    table1 t1
        JOIN    table2 t2
        ON      table1.value = table2.DESC
        WHERE   table1.UPDATETYPE='blah'
        ) src
ON      (trg.rowid = src.rid)
WHEN MATCHED THEN UPDATE
    SET trg.value = code;

Answered by Quassnoi

Solution #3

MERGE WITH THE WHERE CONDITION:

MERGE into table1
USING table2
ON (table1.id = table2.id)
WHEN MATCHED THEN UPDATE SET table1.startdate = table2.start_date
WHERE table1.startdate > table2.start_date;

Because columns mentioned in the ON clause cannot be modified, you’ll require the WHERE clause.

Answered by Roland

Solution #4

Some of the solutions listed above should not be used.

Some people recommend using nested SELECT, but don’t do that because it’s painfully sluggish. If you have a large number of records to update, utilize join, such as:

update (select bonus 
        from employee_bonus b 
        inner join employees e on b.employee_id = e.employee_id 
        where e.bonus_eligible = 'N') t
set t.bonus = 0;

For further information, go to this page. http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx.

Also, make sure that all of the tables you’re connecting have primary keys.

Answered by duvo

Solution #5

 UPDATE ( SELECT t1.value, t2.CODE
          FROM table1 t1
          INNER JOIN table2 t2 ON t1.Value = t2.DESC
          WHERE t1.UPDATETYPE='blah')
 SET t1.Value= t2.CODE

Answered by Morten Anderson

Post is based on https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle