CODEWITHSUNDEEP
sqloracle-databasesql-updatesubqueryinner-join
user1107888
user1107888

Reputation: 1525

Update values from one table in another

I have tables A,B and C. I need to select certain values from all rows in Table A and then for every row in A, I need to update values in Table B and Table C.

Pseudo Code would look like this:

 SELECT A1, A2, A3, A4 FROM Table A 
           UPDATE Table B SET B2=A2, B3=A3, B4=A4 WHERE B1 = A1;
           UPDATE Table C SET C2=A2, C3=A3, C4=A4 WHERE C1 = A1;

How can I achieve this?

Upvotes: 2

Views: 137

Answers (2)

GMB
GMB

Reputation: 222492

You need two update statements. You could use an inline view:

update (
    select a.a2, a.a3, a.a4 , b.b2, b.b3, b.b4
    from a
    inner join b on b.b1 = a.a1
) u
set u.b2 = u.a2, u.b3 = u.a3, u.b4 = u.a4

The upside is that this only updates matching rows - while, using the correlated subquery technique, you need to repeat the subquery in the where clause.

Another neat syntax that does what you want in Oracle is merge:

merge into b
using a on (a.a1 = b.a1)
when matched then update set b.b2 = a.a2, b.b3 = a.a3, b.b4 = a.a4;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269973

In Oracle, you would use two update statements:

update b
    set (b2, b3, b4) = (select a2, a3, a4 from a where a.a1 = b.b1);

update c
    set (c2, c3, c4) = (select a2, a3, a4 from a where a.a1 = c.b1);

Upvotes: 2

Related Questions