SQL | UPDATE with JOIN
Last Updated :
21 Aug, 2020
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 |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...