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

The code you have mentioned is not valid in ORACLE.

You can try the code given below:

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

Want to learn SQL from scratch? Here's is the right video for you on SQL provided by Intellipaat

Or else you can use this code:

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