Removing rows from multiple tables in MySQL

Question

Here is what I am trying to do:

Remove the project from the projects table and all the images associated with this project in the images table.

Say $del_id = 10

 DELETE FROM projects, images WHERE projects.p_id = '$del_id' AND images.p_id = '$del_id' 

What is wrong with this request?

Answer 1

 DELETE projects, images FROM projects, images WHERE projects.p_id = '$del_id' AND projects.p_id = images.p_id; 

Answer 2

As Chacha102 noted , the problem with your request was AND in the WHERE .

However, you can use the JOIN syntax for multi-table DELETE s, which is easier for me to read:

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

Answer 3

 $sql = "DELETE FROM projects, images WHERE projects.p_id = '".$del_id."' or images.p_id = '".$del_id."'"; 

When deleted, an element will never meet both of these requirements, so it should be OR not AND

Answer 4

Answer

 <?php $query = sprintf(" DELETE FROM p, i USING projects p, images i WHERE p.p_id = %d AND p.p_id = i.p_id ", $del_id); ?> 

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 | -- +------+------+ 

remove

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

result

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

works with pleasure!

Answer 5

To do this, you must use two separate queries:

 delete from images where p_id = 123; delete from projects where p_id = 123; 

i.e.

• First remove project-specific images (foreign key?)
• And, when nothing else depends on the project, delete the project itself.

And as a precaution, you have to wrap it all in a transaction to get it all or nothing - if you use a storage engine that supports transactions like InnoDb.

See 12.3.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax , more on this in the MySQL Manual.

Answer 6

Change AND to OR.

You might want to use a foreign key constraint with cascading deletion, a lot easier, but you should use innoDB and create this FK constraint. Delete the project and all images associated with it will also be deleted.

Answer 7

(Wrong answer, MySQL allows this )

You cannot delete from two tables in one query.

The closest you can get is transferring two deletes to a transaction:

 begin transaction delete from projects where p_id = $del_id delete from images where p_id = $del_id commit transaction