Update data in one table with data from another table

Updating Multiple Columns in a Table Using Values from Another Table - A Cross-Platform Approach

In relational database management systems (RDBMS), updating multiple columns in one table with values from another is a common task. Let’s explore how to achieve this in three widely-used systems: SQL Server, MySQL, and PostgreSQL. For this demonstration, we have two tables, TableA and TableB, linked by a foreign key relationship.

Table Structures and Data

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

The goal is to update columns b, c, and d in TableA from the corresponding columns in TableB, based on the foreign key relationship (TableA.a = TableB.a1) and an additional condition (TableB.e1 > 40).

SQL Server

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

Results after the update:

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

In SQL Server, the UPDATE...FROM...JOIN syntax allows us to join the tables and specify the columns to be updated based on the given conditions.

MySQL

UPDATE TABLEA
JOIN TABLEB ON TABLEA.a = TABLEB.a1
SET TABLEA.b = TABLEB.b1, TABLEA.c = TABLEB.c1, TABLEA.d = TABLEB.d1
WHERE TABLEB.e1 > 40;

Results after the update:

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

MySQL uses a similar approach with the UPDATE...JOIN...SET syntax.

PostgreSQL

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

Results after the update:

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

In PostgreSQL, the UPDATE...SET...FROM...WHERE syntax accomplishes the same task.

By understanding the nuances of each RDBMS syntax, you can seamlessly perform such updates across various database systems.