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.
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
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
--
happy programming
No comments:
Post a Comment