Update Join in SQL
Great question! UPDATE JOIN in SQL is a technique where you update rows in one table based on related data from another table by performing a join inside an UPDATE statement.
What is UPDATE JOIN?
It updates columns in a table using values from a related table.
You join the target table with another table to decide which rows to update and what values to set.
Syntax Examples by SQL Dialect
1. SQL Server
UPDATE t1SET t1.ColumnToUpdate = t2.ColumnWithNewValueFROM Table1 t1JOIN Table2 t2 ON t1.ID = t2.IDWHERE <optional_condition>;2. MySQL
UPDATE Table1 t1JOIN Table2 t2 ON t1.ID = t2.IDSET t1.ColumnToUpdate = t2.ColumnWithNewValueWHERE <optional_condition>;3. PostgreSQL
PostgreSQL uses a FROM clause similar to SQL Server:
UPDATE Table1 t1SET ColumnToUpdate = t2.ColumnWithNewValueFROM Table2 t2WHERE t1.ID = t2.IDAND <optional_condition>;Example
Suppose you want to update the salary of employees in Employees table based on a Raises table:
| Employees | Raises |
|---|---|
| EmployeeID | Salary |
| 1 | 50000 |
| 2 | 60000 |
SQL Server / PostgreSQL
UPDATE eSET e.Salary = e.Salary + r.RaiseAmountFROM Employees eJOIN Raises r ON e.EmployeeID = r.EmployeeID;MySQL
UPDATE Employees eJOIN Raises r ON e.EmployeeID = r.EmployeeIDSET e.Salary = e.Salary + r.RaiseAmount;Notes
The exact syntax varies slightly depending on the database system.
You must have a condition to match rows correctly; otherwise, it may update unintended rows.
Make sure the join condition correctly links the tables.
If you want, I can tailor an example to your specific database or use case!
