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:

Thanks a lot sir ur coding help me so much

Another good link for this :

http://neeraj-garg.blogspot.com/2009/03/how-to-update-values-of-one-column-of.html

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)

Riju : Thanks a lot....

Post a Comment