Udacity

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 (
 Japanese VARCHAR(50),
 English VARCHAR(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 ;

Japanese	English
Shime Saba	Cured Mackerel
Saba Oshizushi	Pressed Mackerel
Shime Saba Bo	Marinated Mackerel
Maguro Zuke	Marinated Tuna
Aburi Toro	Seared Tuna Sashimi

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 ;

Japanese	English
Shime Saba	Cured Mackerel
Saba Oshizushi	Pressed Mackerel
Shime Saba Bo	Marinated Mackerel
Maguro Zuke	Marinated Tuna

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 ;

Japanese	English
Maguro Zuke	Marinated Tuna

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:

Wildcard	Matches	Example
-	a range of characters	[b-c]at finds bat, cat, but not rat
!	characters not in brackets	b[!o]t finds bat, but, but not bot
?	any single character	?at finds rat but not rate
[]	any single character in brackets	[cr]at finds cat and rat
* or %	zero or more characters	%angle% finds bangles and dangle
#	any single numeric character	1#3 finds 103, 113, but not 1234

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 (
 dish VARCHAR(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 ;

Japanese	English
Saba Oshizushi	Pressed Mackerel
Shime Saba Bo	Marinated Mackerel
Aburi Toro	Seared Tuna Sashimi

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.