Update data in one table with data of another table

In this programming tutorial we will learn how to update data in one table with data of another table. Let's have a look over the example given below.

Update data in one table with data of another table

Table Structures and values:

TableA consists of four columns: a, b, c, d (a is the primary key)

TableB consists of five columns: a1, b1, c1, d1, e1 (a1 and b1 together made 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:

TableA

a b c d
1 x y z
2 a b c
3 t x z

TableB

a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50

Now our requirement is to write a query 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. So let's have a look over the query written below for this task.

UPDATE TABLEA SET b = TABLEB.b1, c = TABLEB.c1, d = TABLEB.d1 FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40

When you execute this query then you will find the output given below.

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

So that's it. I hope you will find this tutorial very handy.

I love your feedback.