How to Delete With Join in MySQL

1. Limitations in Using the DELETE FROM Statement in MySQL
2. Using the INNER JOIN Method in MySQL
3. the Final Outcome in Using JOIN to Delete With MySQL

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: