SQL Update One Table If Record Does Not Exist In Another Table

SELECT c.*
FROM customers c LEFT JOIN
     invoices i
     ON i.customer_id = c.id
WHERE i.customer_id IS NULL

The above works to give me all the customer accounts that have no invoices. It takes a long time to run, but I’m not concerned with the speed. I will likely only run this a couple times a year.

What I can’t get right is updating a record in the customers table when the account has no invoices. I have tried a number of different ways to accomplish this but always get a syntax error.

One attempt is below…

UPDATE c
    SET active=0
    FROM customers c LEFT JOIN
         invoices i
         ON i.customer_id = c.id
    WHERE i.customer_id IS NULL

I get a syntax error in the Join when I try to run this.

Answer

The correct MySQL syntax is:

UPDATE customers c LEFT JOIN
       invoices i
       ON i.customer_id = c.id
    SET active = 0  
WHERE i.customer_id IS NULL;

The use of JOIN in an UPDATE is rather database-specific. For instance, MySQL doesn’t support the FROM clause in an UPDATE (SQL Server and Postgres do).

Standard syntax that should work in any database is:

UPDATE customers
    SET active = 0
    WHERE NOT EXISTS (SELECT 1 FROM invoices i WHERE i.customer_id = customers.id);