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

Answer 2

Oracle doesn’t support UPDATE statements with INNER JOIN syntax like MySQL does. Instead, you can rewrite the query using a subquery with a MERGE statement or a correlated subquery in Oracle. Here’s how you can do it using a MERGE statement, which is one of the most common methods in Oracle:

Using a MERGE Statement:

MERGE INTO table1 t1

USING table2 t2

ON (t1.value = t2.DESC)

WHEN MATCHED THEN

   UPDATE SET t1.value = t2.CODE

   WHERE t1.UPDATETYPE = 'blah';

Using a Correlated Subquery:

Alternatively, if you prefer an UPDATE statement, you can use a correlated subquery to achieve similar results:

UPDATE table1 t1

SET t1.value = (SELECT t2.CODE

               FROM table2 t2

               WHERE t2.DESC = t1.value)

WHERE t1.UPDATETYPE = 'blah'

AND EXISTS (SELECT 1

           FROM table2 t2

           WHERE t2.DESC = t1.value);

Both of these approaches should work in Oracle without causing the ORA-00933 error. The MERGE statement can be more efficient for large datasets, while the correlated subquery is closer in style to the MySQL syntax you were using.

Answer 3

You have encountered the error because Oracle doesn’t support the INNER JOIN within the UPDATE clause. To achieve the same in Oracle you can use the subquery. 

Query:

UPDATE table1

SET table1.value = (

    SELECT table2.CODE

    FROM table2

    WHERE table1.value = table2.DESC

)

WHERE table1.UPDATETYPE = 'blah';