SQL UPDATE With JOIN

UPDATE Customers C
JOIN Orders O ON C.customer_id = O.customer_id
SET C.last_name = 'Smith'
WHERE O.item = '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.