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

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.