MOCKSTACKS
EN
Questions And Answers

More Tutorials









MYSQL DELETE

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
(1,'Kathy','f'),(2,'John','m'),(3,'Paul','m'),(4,'Kim','f');
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'),
(1,'Rover2','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

DELETE p2
FROM pets p2
WHERE p2.ownerId in (
 SELECT p1.id
 FROM people p1
 WHERE p1.name = 'Paul');

can be rewritten as:

DELETE p2 -- remove only rows from pets
FROM people p1
JOIN pets p2
ON p2.ownerId = p1.id
WHERE p1.name = '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 = p1.id
WHERE p1.name = '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
DELETE CASCADE;

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:

DELETE FROM people
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 = p1.id WHERE p1.name = 'Paul';
SET foreign_key_checks = 1;


Conclusion

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: mockstacks@gmail.com.


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 Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.