SQL | UPDATE with JOIN

SQL UPDATE JOIN could be used to update one table using another table and join condition.

Syntax –

UPDATE tablename  
INNER JOIN tablename  
ON tablename.columnname = tablename.columnname  
SET tablenmae.columnnmae = tablenmae.columnname;

Use multiple tables in SQL UPDATE with JOIN statement.

Let us assume we have two tables – Geeks1 and Geeks2. To check the content in the table –

SELECT * 
FROM Geeks1;

Table – Geeks1

col1	col2	col3
1	11	FIRST
11	12	SECOND
21	13	THIRD
31	14	FOURTH

SELECT * 
FROM Geeks2;

Table – Geeks2

col1	col2	col3
1	21	TWO-ONE
11	22	TWO-TWO
21	23	TWO-THREE
31	24	TWO-FOUR

Example –

We have table Geeks2 which has two rows where Col 1 is 21 & 31 and we want to update the value from table Geeks2 to table Geeks1 for the rows where Col 1 is 21 and 31. Also, we want to update the values of Col 2 and Col 3 only.

UPDATE Geeks1  
SET col2 = Geeks2.col2,  
col3 = Geeks2.col3  
FROM Geeks1  
INNER JOIN Geeks2 ON Geeks1.col1 = Geeks2.col1  
WHERE Geeks1.col1 IN (21, 31);

Output –

(2 row(s) affected)

SELECT * 
FROM Geeks1;

Table – Geeks1

col1	col2	col3
1	11	FIRST
11	12	SECOND
21	23	TWO-THREE
31	24	TWO-FOUR

SELECT * 
FROM Geeks2;

Table – Geeks2

col1	col2	col3
1	21	TWO-ONE
11	22	TWO-TWO
21	23	TWO-THREE
31	24	TWO-FOUR