Update statement with inner join on Oracle

Question

I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

The query is:

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

Answer 1

That syntax isn't valid in Oracle. You can do this:

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); 

Or you might be able to do this:

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 ).