CODEWITHSUNDEEP
sqloracle-database
user10111685
user10111685

Reputation:

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
;

Upvotes: 0

Views: 41

Answers (3)

alexherm
alexherm

Reputation: 1362

Use the MERGE command.

Here is a basic example:

    MERGE INTO table1 a
    USING (SELECT bb.asdf,cc.zxcv
           from table2 bb,table3 cc
           where bb.field1=cc.field1) b
    ON (a.asdf=b.asdf)
    WHEN MATCHED THEN
    UPDATE SET a.zxcv=b.zxcv;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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
                 );

Upvotes: 0

James
James

Reputation: 3015

Maybe something like this?

update B
set B.id=A.Id
from table1 B
  join table2 A
    on B.num=A.num
   and B.name=A.name
   and B.id<>A.id

Upvotes: 1

Related Questions