Mohammad Amin Kaviani
Mohammad Amin Kaviani

Reputation: 21

How to update from other table in Oracle's sql

I have two table and I want update table A from Table B in Oracle's sql

table A
customer_id    geo_id     geo
1234567890       3521     texas
0987654321       3624     dallas
1597536842       3121     mexicocity
table B
geo_id        customer_id
8745          1234567890
2145          0987654321
3699          1597536842
update table A
set   geo_id   = (select geo_id from table B)
where tableA.customer_id = tableB.customer_id;

Upvotes: 1

Views: 60

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31746

Use MERGE statement

MERGE INTO tablea a 
     using tableb b ON( a.customer_id = b.customer_id ) 
WHEN matched THEN 
  UPDATE SET a.geo_id = b.geo_id 

OR a Correlated update

update tablea a set 
    a.geo_id = (select geo_id from 
                      tableb b 
                      where a.customer_id = b.customer_id)

DEMO

Upvotes: 4

Related Questions