Update statement with inner join on Oracle

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

Use this:

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

MERGE with WHERE clause:

MERGE into table1USING table2ON (table1.id = table2.id)WHEN MATCHED THEN UPDATE SET table1.startdate = table2.start_dateWHERE table1.startdate > table2.start_date;

You need the WHERE clause because columns referenced in the ON clause cannot be updated.