SQL Delete — All Good Things Must End

Relational databases store information in tables — with columns that are analogous to elements in a data structure and rows which are one instance of that data structure — which are brought into existence via the SQL Create Table statement. 

The SQL Delete statement is used to remove rows from data from the tables. It looks like:

DELETE FROM table WHERE condition ;

Be exceedingly careful around the WHERE clause; an error in restricting the scope of deletion can have shocking consequences. (Consider making a table backup with the SQL Create Table From statement.)

Setting the Environment

Let’s create and populate a table with sushi names in Japanese and the corresponding English.

CREATE TABLE sushi (
JapaneseVARCHAR(50),
EnglishVARCHAR(90) ) ;
 
INSERT INTO sushi
VALUES
( "Shime Saba", "Cured Mackerel" ),
( "Saba Oshizushi", "Pressed Mackerel" ),
( "Shime Saba Bo", "Marinated Mackerel" ),
( "Maguro Zuke", "Marinated Tuna" ),
( "Aburi Toro", "Seared Tuna Sashimi" ) ;

Let’s take stock of the table before we start deletions.

SELECT * FROM sushi ;

A Typical SQL Delete with WHERE Clause

A typical WHERE clause matches a value exactly, such as a social security number or email address. Deleting the “Aburi Toro” sushi only looks like this:

DELETE FROM sushi WHERE Japanese = "Aburi Toro" ;
SELECT * FROM sushi ;

A SQL Delete With a Wildcard LIKE Clause

Sometimes bulk deletions are called for; perhaps the mackerel has sold out. Continuing with the previous table, in which we’ve deleted “Aburi Toro”, we then delete all menu options with that ingredient by:

DELETE FROM sushi WHERE English LIKE "%Mackerel%" ;
SELECT * FROM sushi ;

In the example above, the wildcard percent character % matches zero or more characters. Check your documentation as supported wildcards vary between databases. Typical wildcards include:

Additional SQL Delete WHERE Clauses

Subqueries as WHERE clauses can be very flexible, especially when we blend together wildcards and the ability to process lists of values to match. For example, If we maintain  a table of sold-out dishes:

CREATE TABLE sold_out_dishes (
dishVARCHAR(50) ) ;

INSERT INTO sold_out_dishes
VALUES
( "Cured Mackerel" ), ( "Marinated Tuna" )  ;

Then we’re able to remove dishes from the menu with a WHERE IN clause:

DELETE FROM sushi
WHERE English IN (
SELECT dish
FROM sold_out_dishes ) ;
SELECT * FROM sushi ;

A SQL Delete Without a WHERE Clause

Using a SQL Delete without a WHERE clause will delete every row in the table. This may be the intended effect, as if we’re clearing out the menu to begin anew. Double-check your WHERE clauses before inadvertently deleting wanted data.

DELETE FROM sushi ;

Checking the results shows no rows exist anymore.

SELECT * FROM sushi ;
0 rows returned.

Conclusion

SQL Delete removes rows of data from database tables. The WHERE clause limits which rows are deleted. There are literal clauses, which match a value exactly, and wildcard clauses, which match values based upon a pattern.

Be mindful with the WHERE clause; an error in restricting the scope of deletion will cause unexpected data loss. (Consider making a table backup with the SQL Create Table From statement.)

Start Learning

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.