Tuesday, April 15, 2008

Update data in one table with data from another table

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


Results after the update:

a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z

7 comments:

Thakur said...

Thanks a lot sir ur coding help me so much

Neeraj Garg said...

Another good link for this :
http://neeraj-garg.blogspot.com/2009/03/how-to-update-values-of-one-column-of.html

Vivek Gupta said...
This comment has been removed by the author.
Vivek Gupta said...
This comment has been removed by the author.
Vivek Gupta said...

another best link for this post is here check it :-

c-sharp-corner (update a table from another table's data with a condition in sql server)

Unknown said...

Riju : Thanks a lot....

Ivan Bauer said...
This comment has been removed by the author.