Update data in one table with data from another table

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in the three RDBMS that we support.

Table Structures and values:

TableA has four columns: a, b, c, d (a is the primary key column)

TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:

I.    TableA

a    b    c    d

1    x    y    z

2    a    b    c

3    t    x    z

II.    TableB

a1    b1    c1    d1    e1

1    x1    y1    z1    40

2    a1    b1    c1    50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:

UPDATE TABLEA

SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

/

Results after the update:

a    b    c    d

————————————

1     x          y           z

2     a1        b1         c1

3     t           x           z

SQL Server:

UPDATE TABLEA

SET     b = TABLEB.b1,

c = TABLEB.c1,

d = TABLEB.d1

FROM TABLEA, TABLEB

WHERE TABLEA.a = TABLEB.a1

AND TABLEB.e1 > 40

GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.

Results after the update:

a    b    c    d

————————————

1     x          y           z

2     a1        b1         c1

3     t           x           z

DB2 LUW:

–Same as Oracle–

UPDATE TABLEA

SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40);

Results after the update:

a    b    c    d

————————————

1     x          y           z

2     a1        b1         c1

3     t           x           z

NOTE:

It is very important to make sure that your where clause for the update statement is correct since that is what identifies the records that the update statement is going to qualify and do the update upon.  If it is incorrect, then you can get wrong results.  The reason I am mentioning this is because I have seen people write wrong where clauses and then wondering what went wrong because they specified the correct condition in the SET clause.

In the above example, if the Where condition was omitted, the other record’s columns would be updated to NULL value and this will be the final result set:

a    b    c    d

————————————

1     Null      Null      Null

2     a1        b1         c1

3     Null     Null      Null