Deleting rows from multiple tables in MySQL

Question

I am trying to delete a project from the projects table and all the images associated with that project in the images table.

Lets say p_id = 10

DELETE FROM projects, images WHERE projects.p_id = ? AND images.p_id = ?

What is wrong with this query?

Answer 1

DELETE projects, images 
FROM projects, images 
WHERE projects.p_id = ? 
AND projects.p_id = images.p_id;

Answer 2

DELETE FROM projects, images WHERE projects.p_id = ? or images.p_id = ?

When being deleted, an item will never meet both of these requirements, therefore it must be OR not AND

Answer 3

As Chacha102 noted, the problem of your query was the AND in the WHERE clause.

However, you may want to use the JOIN syntax for multi-table DELETEs, which I find easier to read:

DELETE     projects, images
FROM       projects 
LEFT JOIN  images ON images.p_id = projects.p_id
WHERE      projects.p_id = 10;

Answer 4

The answer

DELETE FROM p, i
USING projects p, images i
WHERE p.p_id = ?
  AND p.p_id = i.p_id

The test

projects

create table projects (
  p_id int unsigned not null auto_increment primary key
);
insert into projects (p_id) values (1),(2),(3);
select * from projects;
-- +------+
-- | p_id |
-- +------+
-- |    1 | 
-- |    2 | 
-- |    3 | 
-- +------+

images

create table images (
  i_id int unsigned not null auto_increment primary key,
  p_id int unsigned default null
);
insert into images (p_id) values (1),(1),(1),(2),(2),(3),(3);
select * from images;
-- +------+------+
-- | i_id | p_id |
-- +------+------+
-- |    1 |    1 | 
-- |    2 |    1 | 
-- |    3 |    1 | 
-- |    4 |    2 | 
-- |    5 |    2 | 
-- |    6 |    3 | 
-- |    7 |    3 | 
-- +------+------+

the delete

delete from p, i
using projects p, images i
where p.p_id = i.p_id
 and  p.p_id = 1;

the result

select * from projects;
-- +------+
-- | p_id |
-- +------+
-- |    2 | 
-- |    3 | 
-- +------+

select * from images;
-- +------+------+
-- | i_id | p_id |
-- +------+------+
-- |    4 |    2 | 
-- |    5 |    2 | 
-- |    6 |    3 | 
-- |    7 |    3 | 
-- +------+------+

works a treat!

Answer 5

You should use two separate queries to do that :

delete from images where p_id = 123;

delete from projects where p_id = 123;

i.e. :

• First, delete the images, that depend on the project (foreign key ? )
• And, when nothing depends on the project anymore, delete the project itself.

And, as a security precaution, you should wrap all this in a transaction, to get a all or nothing behavior -- well, if you are using a storage engine that suppors transactions, like InnoDb.

See 12.3.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax, about that, in the MySQL Manual.

Answer 6

Change the AND into an OR.

You might want to use a foreign key constraint with a cascading delete, much easier, but you have to use innoDB and create this FK-constraint. Delete the project and all related images will be deleted as well.

Answer 7

(Wrong answer, MySQL allows this)

You can't delete from two tables in one query.

The closest you can get is wrap the two deletes in a transaction:

begin transaction
delete from projects where p_id = ?
delete from images where p_id = ?
commit transaction