Example
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;
