JIYIK CN >

This tutorial article will show you how to use MySQL JOINmethods to delete data from multiple tables. This is useful when you are simultaneously deleting records from one table that are related to a specific record in another table.

DELETE FROMLimitations of using the statement in MySQL

DELETE FROMThe statement is used to delete rows from a table. You can apply WHEREto specify the rows and conditions to be met when deleting.

The following code contains two tables, the first records the vendor's products and their locations, and the second records the list of store owners who sell these products:

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 stores in the table are located in the city center, so in this case Uptownit does not make sense to maintain supplier records for the region.

You can use it DELETEon only one table at a time. You can shopsget data from the table, specify conditions, and then delete supplierall records in the table that match all the conditions.

In this case, we will use delete all the records JOINfrom supplierthe table .Uptown

INNER JOINUsing the method in MySQL

Using INNER JOINthe method will allow you to merge two or more tables and delete multiple rows at once.

There are other options, such as using a subquery, but this is a more practical way to get the job done. Using the example tables above, you can delete Uptownall rows that have a value of in both tables.

The following code will find the location of the store and keep the suppliers in Downtownthe region, while removing Uptownthe supplier registered as :

DELETE supplier FROM supplier
INNER JOIN shops ON supplier.category = shops.shptype
WHERE supplier.location != shops.location;

The tables are joined based on fields that share similar data. The Category field shows in this case what the supplier supplied and which stores sold it.

Merging the two tables will allow you to define only one deletion condition. In this case, the supplier's position will determine which rows to delete.

You can also DELETEdelete both tables at the same time by adding the table name after . This will work if you have multiple records in multiple tables.

DELETE supplier, shops FROM supplier
...

JOINThe final result of deleting MySQL using

The result will keep your table intact while removing all records for suppliers that do not meet the above criteria.

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 a store by location, in this case Downtown, and removes all rows from the suppliers table that do not match the criteria.

From the following SELECTtest, we can see supplierthat the table now only contains Downtownsuppliers listed as :

SELECT * FROM supplier

result: