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 – Beginner1 and Beginner2. To check the content in the table –

SELECT * 
FROM Beginner1;

SELECT * 
FROM Beginner2;

Example –

We have table Beginner2 which has two rows where Col 1 is 21 & 31 and we want to update the value from table Beginner2 to table Beginner1 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 Beginner1  
SET col2 = Beginner2.col2,  
col3 = Beginner2.col3  
FROM Beginner1  
INNER JOIN Beginner2 ON Beginner1.col1 = Beginner2.col1  
WHERE Beginner1.col1 IN (21, 31);

Output –

(2 row(s) affected)

SELECT * 
FROM Beginner1;

SELECT * 
FROM Beginner2;