CODEWITHSUNDEEP
sqloracle-databasesql-update
Ashish Bhavsar
Ashish Bhavsar

Reputation: 236

ORACLE : Update query using two table with has relation

I need to update the column data based on matching record found in both Table. I want to update the record of NAME column from TABLE2

Following are the tables

 Table1
---------------
 Id | Name | color
 1  | abc  | red
 2  | def  | green
 3  | ghi  | blue


 Table2
---------------
 Id | Name | color |fiedId
 1  | abc  | red   | 1
 2  | def  | green | 1
 3  | ghi  | blue  | 2

Here table1 ID column is the Foreign Key in table2 as fieldId.

So, I want to update all the record which fall under this condition 

table1.id = table2.fieldId

Upvotes: 0

Views: 864

Answers (2)

William Robertson
William Robertson

Reputation: 16001

Sounds like you just want an update like this:

update table2 t2
set    t2.name =
       ( select t1.name
         from   table1 t1
         where  t1.id = t2.fieldid )

Regarding the followup question:

what if i want to set Name = "xxx" for all matching rows?

update table2 t2
set    t2.name = 'xxx'
where  t2.fieldid in
       ( select t1.id from table1 t1 )

or this can be written as:

update table2 t2
set    t2.name = 'xxx'
where  exists
       ( select null from table1 t1
         where  t1.id = t2.fieldid )

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143023

Yet another option, using MERGE:

merge into table2 t2
using (select id, name from table1) x
on (t2.fieldid = x.id)
when matched then update set
  t2.name = x.name;

Or, for setting the name to 'xxx':

merge into table2 t2
using (select id from table1) x
on (t2.fiedid = x.id)
when matched then update set
  t2.name = 'xxx';

Upvotes: 1

Related Questions