Questions And Answers

More Tutorials


Multi-Table Deletes

MySQL's DELETE statement can use the JOIN construct, allowing also to specify which tables to delete from. This is useful to avoid nested queries. Given the schema:

create table people
( id int primary key,
 name varchar(100) not null,
 gender char(1) not null
insert people (id,name,gender) values
create table pets
( id int auto_increment primary key,
 ownerId int not null,
 name varchar(100) not null,
 color varchar(100) not null
insert pets(ownerId,name,color) values
(1,'Rover','beige'),(2,'Bubbles','purple'),(3,'Spot','black and white'),

id name gender

1 Kathy f
2 John m
3 Paul m
4 Kim f

id ownerId name color

1 1 Rover beige
2 2 Bubbles purple
4 1 Rover2 white

If we want to remove Paul's pets, the statement

FROM pets p2
WHERE p2.ownerId in (
 FROM people p1
 WHERE = 'Paul');

can be rewritten as:

DELETE p2 -- remove only rows from pets
FROM people p1
JOIN pets p2
ON p2.ownerId =
WHERE = 'Paul';

1 row deleted
Spot is deleted from Pets

p1 and p2 are aliases for the table names, especially useful for long table names and ease of readability.

To remove both the person and the pet:

DELETE p1, p2 -- remove rows from both tables
FROM people p1
JOIN pets p2
ON p2.ownerId =
WHERE = 'Paul';

2 rows deleted
Spot is deleted from Pets
Paul is deleted from People

foreign keys

When the DELETE statement involes tables with a foreing key constrain the optimizer may process the tables in an order that does not follow the relationship. Adding for example a foreign key to the definition of pets

ALTER TABLE pets ADD CONSTRAINT `fk_pets_2_people` FOREIGN KEY (ownerId) references people(id) ON

the engine may try to delete the entries from people before pets, thus causing the following error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`test`.`pets`, CONSTRAINT `pets_ibfk_1` FOREIGN KEY (`ownerId`) REFERENCES `people` (`id`))

The solution in this case is to delete the row from people and rely on InnoDB's ON DELETE capabilities to propagate the deletion:

WHERE name = 'Paul';

2 rows deleted
Paul is deleted from People
Spot is deleted on cascade from Pets

Another solution is to temporarily disable the check on foreing keys:

SET foreign_key_checks = 0;
DELETE p1, p2 FROM people p1 JOIN pets p2 ON p2.ownerId = WHERE = 'Paul';
SET foreign_key_checks = 1;


In this page (written and validated by ) you learned about MYSQL DELETE . What's Next? If you are interested in completing MYSQL tutorial, your next topic will be learning about: MYSQL UPDATE.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue:

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using, you agree to have read and accepted our terms of use, cookies and privacy policy.