SQL UPDATE With JOIN

In SQL, using an UPDATE statement with JOIN allows us to modify data in one table based on values in another table.

Example

UPDATE Customers C
JOIN Orders O ON C.customer_id = O.customer_id
SET C.last_name = 'Smith'
WHERE O.item = 'Keyboard';

Here, the SQL command joins the Customers and Orders tables. Then, it updates the last_name column in the Customers table to Smith for all customers who have ordered a keyboard.


UPDATE With INNER JOIN

Using INNER JOIN within an UPDATE statement is useful for precise modifications when a match is found in both tables.

Let's look at an example.

UPDATE Customers C
JOIN Shippings S ON C.customer_id = S.customer
SET C.age = C.age + 1
WHERE S.status = 'Pending';

This SQL command increases the age of customers by 1 in the Customers table if their shipping status is Pending in the Shippings table.

Note: Our online compiler is based on SQLite, which doesn't support combining the UPDATE and JOIN statements.


UPDATE With LEFT JOIN

LEFT JOIN within an UPDATE statement allows for updates even when there's no matching record in the joined table. For example,

UPDATE Customers C
LEFT JOIN Orders O ON C.customer_id = O.customer_id
SET C.country = 'Unknown'
WHERE O.customer_id IS NULL;

This command updates the country column to Unknown for customers in the Customers table who have no matching records in the Orders table.


UPDATE With Multiple JOINs

We can also use multiple JOIN queries with a single UPDATE statement for complex scenarios. For example,

UPDATE Customers C
JOIN Orders O ON C.customer_id = O.customer_id
JOIN Shippings S ON O.order_id = S.shipping_id
SET C.first_name = 'Alice'
WHERE S.status = 'Delivered' AND O.item = 'Monitor';

Here, the SQL query updates the first_name to Alice in the Customers table for those who ordered a Monitor and whose shipping status is Delivered.


UPDATE With Subquery

Using a subquery within the WHERE clause can mimic the JOIN behavior in SQLite. For example,

UPDATE Customers
SET age = age + 1
WHERE customer_id IN (
    SELECT customer
    FROM Shippings
    WHERE status = 'Pending'
);

SELECT * FROM Customers;

This command increases the age of customers in the Customers table whose shipping status is Pending in the Shippings table.

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges