The Subquery In a Delete Statement

NOTE: Before you can run a DELETE or UPDATE statement without a WHERE clause, you must uncheck “Safe Updates” checkbox in MySQL Preference. Please see below.

Code Sample:

USE world;
DELETE FROM city_bak
WHERE CountryCode IN
    (SELECT code FROM country
        WHERE region = 'Central Africa');

Results:

USE world;

• The tables used in this example are in the world database. Make sure it is selected as the default

DELETE FROM city_bak

• We are going to execute a DELETE statement on the city_bak table

WHERE CountryCode IN

• We are going to use a filter to delete items from the city_bak table where the CountryCode is found in a list of values that we will pass to it.

(SELECT code FROM country

     WHERE region = 'Central Africa');

• We will execute a subquery on the country table and return a list of code values (PK to FK in city_bak table) where the region is equal to ‘Central Africa’.
• You could accomplish the same thing by joining the city_bak table to the country table, then filtering on the region column from the country table.