SQL – UPDATE using JOIN for Inserting data into the table from another table!

UPDATE JOIN statement to perform the cross-table update. I will explain the usage of INNER JOIN  clause and LEFT JOIN  clause with the UPDATE statement. Sometimes, it is required to map the relationship between two tables using multiple UNIQUE columns available in both the tables with PRIMARY KEY column. Most of the time, it is a one-to-many relationship.

Syntax:

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2,     T2.C3 = expr
WHERE condition

Example:

Consider, We want to update TMID Column in Doctor Table with the PK of tm Table. Both the Tables have EmpID and EmpTMID as Unique key that can be used for the mapping of both Tables. The tables are given below:

Doctor Table Structure
TM Table Structure

SQL UPDATE Query:


update Doctor inner join tm on tm.EmpID = Doctor.EmpTMID set Doctor.TMID = tm.TMID where Doctor.TMID = 0

Notes:

  • We have already set the default value for Doctor.TMID to 0, to be used in Where Clause