CODEWITHSUNDEEP
mysqlsql
user3221479
user3221479

Reputation: 67

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.

Upvotes: 5

Views: 9489

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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);

Upvotes: 9

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

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

Upvotes: 3

Related Questions