Oracle - Update statement with inner join

Question

I have a query which works fine in MySQL, I'm trying to get it working on oracle but 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';

I'd be extremely grateful for any help.

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, table2.CODE
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
)
SET table1.value = table2.CODE

(It depends if the inline view is considered updateable by Oracle).

Answer 2

 UPDATE ( SELECT t1.value, t2.CODE
          FROM table1 t1
          INNER JOIN table2 t2 ON t1.Value = t2.DESC
          WHERE t1.UPDATETYPE='blah')
 SET t1.Value= t2.CODE

Answer 3

Oracle does not support joins in the UPDATE statements.

Use this:

MERGE
INTO    table1
USING   (
        SELECT  t1.rowid AS rid, t2.code
        FROM    table1 t1
        JOIN    table2 t2
        ON      table1.value = table2.DESC
        WHERE   table1.UPDATETYPE='blah'
        )
ON      rowid = rid
WHEN MATCHED THEN
UPDATE
SET     value = code

Answer 4

Using description instead of desc for table2,

update
  table1
set
  value = (select code from table2 where description = table1.value)
where
  exists (select 1 from table2 where description = table1.value)
  and
  table1.updatetype = 'blah'
;