How to Delete With Join in MySQL
-
Limitations in Using the
DELETE FROM
Statement in MySQL -
Using the
INNER JOIN
Method in MySQL -
the Final Outcome in Using
JOIN
to Delete With MySQL
![How to Delete With Join in MySQL](/img/MySQL/feature-image---delete-with-join-in-mysql.webp)
This tutorial article will show you how to delete data in multiple tables using the MySQL JOIN
method. This is useful when you simultaneously delete records in one table related to a specific record in another.
Limitations in Using the DELETE FROM
Statement in MySQL
The DELETE FROM
statement is used to delete rows in a table. You can apply WHERE
to specify the rows and conditions to meet when deleting.
The following code contains two tables, the first recording supplier commodities and their location, and the second with a list of shop owners who sell these commodities:
Create Table supplier (
suppId INTEGER PRIMARY KEY,
category TEXT NOT NULL,
location TEXT NOT NULL
);
Create Table shops (
owner TEXT NOT NULL,
shptype TEXT NOT NULL,
area TEXT NOT NULL
);
INSERT INTO supplier VALUES (0001, 'Veggies', 'Downtown');
INSERT INTO supplier VALUES (0002, 'Fruits', 'Downtown');
INSERT INTO supplier VALUES (0003, 'Dairy', 'Downtown');
INSERT INTO supplier VALUES (0004, 'Veggies', 'Uptown');
INSERT INTO supplier VALUES (0005, 'Fruits', 'Uptown');
INSERT INTO supplier VALUES (0006, 'Dairy', 'Uptown');
INSERT INTO shops VALUES ('Mark', 'Veggies', 'Downtown');
INSERT INTO shops VALUES ('Mark', 'Fruits', 'Downtown');
INSERT INTO shops VALUES ('Susan', 'Dairy', 'Downtown');
All the shops in the table are based downtown, so in this case, there is no point in maintaining records of suppliers in the Uptown
area.
Using DELETE
will only work for one table at a time. You can take data from the shops
table, specify criteria, and delete all records in the supplier
table that match all the conditions.
In this case, we will remove all records of Uptown
from the supplier
table using JOIN
.
Using the INNER JOIN
Method in MySQL
Using the INNER JOIN
method will allow you to merge two or more tables and delete multiple rows in one go.
There are alternative options, such as using subqueries, but this is a more practical way of getting the job done. Using the example tables above, you can delete all the rows in both tables that have the Uptown
value.
The following code will look for the location of the shops and keep suppliers in the Downtown
area while deleting suppliers registered as Uptown
:
DELETE supplier FROM supplier
INNER JOIN shops ON supplier.category = shops.shptype
WHERE supplier.location != shops.location;
The joining of tables was based on fields that shared similar data. The category field showed what suppliers provided and what shops sold in this case.
Merging the two tables will allow you to define only one condition for deletion. In this case, the location of the supplier will determine what row is to be deleted.
You can also delete both tables simultaneously by simply adding the table’s name after DELETE
. This will work if you have multiple records in more than one table.
DELETE supplier, shops FROM supplier
...
the Final Outcome in Using JOIN
to Delete With MySQL
The outcome will leave your tables intact while simultaneously removing all records of suppliers that do not meet the criteria mentioned above.
Create Table supplier (
suppId INTEGER PRIMARY KEY,
category TEXT NOT NULL,
location TEXT NOT NULL
);
Create Table shops (
owner TEXT NOT NULL,
shptype TEXT NOT NULL,
location TEXT NOT NULL
);
INSERT INTO supplier VALUES (0001, 'Veggies', 'Downtown');
INSERT INTO supplier VALUES (0002, 'Fruits', 'Downtown');
INSERT INTO supplier VALUES (0003, 'Dairy', 'Downtown');
INSERT INTO supplier VALUES (0004, 'Veggies', 'Uptown');
INSERT INTO supplier VALUES (0005, 'Fruits', 'Uptown');
INSERT INTO supplier VALUES (0006, 'Dairy', 'Uptown');
INSERT INTO shops VALUES ('Mark', 'Veggies', 'Downtown');
INSERT INTO shops VALUES ('Mark', 'Fruits', 'Downtown');
INSERT INTO shops VALUES ('Susan', 'Dairy', 'Downtown');
-- delete function
DELETE supplier FROM supplier
INNER JOIN shops ON supplier.category = shops.shptype
WHERE supplier.location != shops.location;
The above matches all suppliers to shops by location, Downtown
in this case, and deletes all rows from the supplier table that do not match the criteria.
From the following SELECT
trial, we can see that the supplier
table now only contains suppliers listed as Downtown
:
SELECT * FROM supplier
Outcome: