SQL UPDATE with JOIN

The SQL UPDATE JOIN statement allows us to update a table by using another table based on a specific condition. This is particularly useful when we have the latest data from an external system and need to update our primary table accordingly.

For example, let’s say we have a users table that needs to be updated with the latest information from a users_backup table. In such a case, we use an INNER JOIN between the target table and the source table using a common identifier, such as the user ID.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

SQL UPDATE JOIN Syntax

UPDATE users u  
INNER JOIN users_backup ub  
ON u.user_id = ub.user_id  
SET u.user_name = ub.user_name,  
    u.email = ub.email  
WHERE u.user_id IN (101, 102);

This query updates the users table with the latest user_name and email from the users_backup table where the user_id matches.

Using Multiple Tables in SQL UPDATE with JOIN

Let’s create two different tables, employees and employees_new, and perform an update operation using JOIN.

Creating Table 1: employees

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_salary INT,
    emp_role VARCHAR(100)
);

INSERT INTO employees (emp_id, emp_salary, emp_role)  
VALUES (101, 50000, 'Developer'),  
       (102, 60000, 'Designer'),  
       (103, 70000, 'Manager'),  
       (104, 80000, 'Director');

Creating Table 2: employees_new

CREATE TABLE employees_new (
    emp_id INT PRIMARY KEY,
    emp_salary INT,
    emp_role VARCHAR(100)
);

INSERT INTO employees_new (emp_id, emp_salary, emp_role)  
VALUES (103, 75000, 'Senior Manager'),  
       (104, 85000, 'Senior Director');

Viewing the Initial Data

To verify our data before updating, run:

SELECT * FROM employees;
SELECT * FROM employees_new;

employees Table Before Update:

emp_id	emp_salary	emp_role
101	50000	Developer
102	60000	Designer
103	70000	Manager
104	80000	Director

employees_new Table Before Update:

emp_id	emp_salary	emp_role
103	75000	Senior Manager
104	85000	Senior Director

Updating Table Using JOIN

Now, let’s update the employees table with the latest salaries and roles from employees_new.

UPDATE employees e  
INNER JOIN employees_new en  
ON e.emp_id = en.emp_id  
SET e.emp_salary = en.emp_salary,  
    e.emp_role = en.emp_role  
WHERE e.emp_id IN (103, 104);

After executing the update, check the table contents:

SELECT * FROM employees;

employees Table After Update:

emp_id	emp_salary	emp_role
101	50000	Developer
102	60000	Designer
103	75000	Senior Manager
104	85000	Senior Director

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Conclusion

The SQL UPDATE JOIN statement is a powerful tool when you need to update one table using another. By using INNER JOIN, we can efficiently merge data and ensure that our database reflects the latest updates. This approach is commonly used in data synchronization, ETL processes, and enterprise applications to keep records accurate and up-to-date.

Start using UPDATE JOIN in your SQL queries to optimize your database updates effortlessly!

---

sql update with join w3schools
sql update with join postgres
mysql update with join
sql update with join oracle
sql update with join and where clause
update query with inner join in sql server
hana sql update with join
snowflake update with join
postgresql update with join
sql update