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:
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.TMIDto 0, to be used inWhereClause