6.4

The Subquery In a Delete Statement

The Subquery in a DELETE statement

  • A subquery can be used in a DELETE statement.
  • Always back up your data and test your DELETE statement before running it on live data.

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.

sub_04.png

Code Sample:

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

Results:

sub_05.png

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.