4
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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
user3221479
  • 57
  • 1
  • 6

2 Answers2

7

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);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

You just made little mistake below query will work

    UPDATE customers  c         
    LEFT JOIN invoices i ON i.customer_id = c.id
    SET active=0
    WHERE i.customer_id IS NULL
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63