SQL | UPDATE with JOIN

In SQL, you can perform an UPDATE with a JOIN to update records in one table based on data from another table. Here’s the general syntax for using UPDATE with a JOIN:

SQL Syntax:

UPDATE table1
JOIN table2 ON table1.column_name = table2.column_name
SET table1.column_to_update = table2.new_value
WHERE condition;

Example:

Let’s assume you have two tables:

1. employees (employee_id, name, department_id, salary)
2. departments (department_id, department_name, department_budget)

You want to increase the salary of employees in the “Sales” department by 10%.

Here’s how you would write the UPDATE query with a JOIN:

UPDATE employees e
JOIN departments d ON e.department_id = d.department_id
SET e.salary = e.salary * 1.10
WHERE d.department_name = 'Sales';

Explanation:

• We’re updating the employees table (aliased as e).
• We join the departments table (aliased as d) on the matching department_id fields.
• We set the salary column in the employees table to be 10% higher (e.salary * 1.10) for employees in the “Sales” department.
• The WHERE clause ensures that only employees in the “Sales” department are updated.