SQL Update From One Table to Another Based on a ID Match

You can use the UPDATE statement with a JOIN to update data in one table based on a match with another table in SQL. Here’s how you can perform an UPDATE from one table to another based on an ID match:

SQL Syntax for Update Using Join

UPDATE table1
SET table1.column_to_update = table2.column_to_use
FROM table1
JOIN table2
ON table1.id = table2.id
WHERE some_condition;

Explanation:

• table1: The table you want to update.
• table2: The table containing the data you want to use to update table1.
• column_to_update: The column in table1 that you want to update.
• column_to_use: The column in table2 from which you will take the new value.
• id: The column used to match the rows between the two tables.
• WHERE some_condition: Optional condition to filter the rows to update. If you want to update all matching rows, you can omit this.

Example:

Assume you have two tables:

• employees: Stores employee details.
• new_salaries: Stores updated salary information for employees.

Table: employees

Table: new_salaries

To update the salary in the employees table with the new_salary from the new_salaries table based on the id, you would write the following SQL query:

UPDATE employees
SET employees.salary = new_salaries.new_salary
FROM employees
JOIN new_salaries
ON employees.id = new_salaries.id;

Result:

After executing the query, the employees table would be updated like this:

As you can see, only the employees whose id matched in both tables (John and Alice) have had their salaries updated.

Notes:

• The JOIN can be done with other types like LEFT JOIN, RIGHT JOIN, etc., depending on your needs.
• If there are multiple rows in table2 matching table1, it may result in multiple updates for the same row in table1.