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.