CODEWITHSUNDEEP
mysqlsql
Draven
Draven

Reputation: 1467

Update multiple tables with single query

All three tables are different. The only thing they have in common is id, and image

This query works only if there are rows in each table.

What's the best way of doing it? Please don't saw use prepared statements. That's on my ToDo list already :)

UPDATE subcontent, products, content
SET subcontent.image = NULL, products.image = NULL, content.image = NULL
WHERE subcontent.image = 3
OR products.image = 3
OR content.image = 'test.png'

I tried using LEFT JOIN but doesn't work either. Is the query wrong?

UPDATE content
LEFT JOIN subcontent on
    subcontent.image = content.image
LEFT JOIN products on
    products.image = content.image
SET content.image = NULL, subcontent.image = NULL, products.image = NULL
WHERE content.image = 'test.png'
OR subcontent.image = 3
OR products.image = 3

Upvotes: 0

Views: 133

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

How about three separate updates?

UPDATE subcontent
   SET subcontent.image = NULL
   WHERE subcontent.image = 3;

UPDATE products
   SET products.image = NULL
   WHERE products.image = 3;

UPDATE content
   SET content.image = NULL
   WHERE content.image = 'test.png';

The problem you have with attempting a multi-table update is that this uses a join. Alas, if one of the tables is empty, then the joining will return no rows in any table to be updates. You can use a left join, but then you have to assume that the first table is not empty. And, MySQL doesn't support a full outer join.

Three separate updates is probably the simplest method.

Upvotes: 1

Related Questions