Updating table with joining to a second table

I’m trying to update the value in column ‘ID’ from table 1 with the value in column ‘ID’ from table 2 – Only if they do not match. I think I have everything except the set statement down.

I’m wondering if this is the best way to go about it and how to format the sub-query for this type of problem

update table1 B
set B.id = (select A.id
                    from table2 A 
                    where  B.num = A.num
                    and B.name = A.name)
where B.num = A.num
and B.name = A.name
and B.id <> A.id
;

Answer

Oracle does not support join in an update. But you can use two subqueries:

update table1 B
    set id = (select A.id
              from table2 A 
              where B.num = A.num and
                    B.name = A.name
             )
    where exists (select A.id
                  from table2 A 
                  where B.num = A.num and
                        B.name = A.name and
                        B.id <> A.id
                 );