Deleting data is the last of the four main functions you perform in SQL. Deleting data can cause a number of issues, so it should be used with caution. For instance, if you don't have integrity and relationships set up, deleting records from one table can cause orphaned records on another. Some administrators don't even allow you to delete records. The alternative is setting a record to active or inactive but never deleting records. If you run your own database or develop software, you'll need to know how to delete records eventually. 

Just like the UPDATE statement, the DELETE statement doesn't technically require a WHERE clause but forgetting the clause can be catastrophic for your data. If you use the DELETE and forget a WHERE clause, SQL identifies the statement as valid and deletes all records in the table. 

The following is the basic requirements for the DELETE statement. 

DELETE FROM <table> 

The above statement deletes all rows. To avoid the mistake of deleting all records, the following is the DELETE statement with a WHERE clause. 

DELETE FROM <table>

WHERE clause 

We have the following table of Customer data. 

CustomerId	First_name	Last_name	City	State
123	Tim	Smart	Miami	FL
321	Frank	Doe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX
459	Ed	Thompson	Atlanta	GA

Suppose you want to delete one customer. You want to delete CustomerId 123 because you no longer need it in the table. Note that deleting a customer that links to other tables such as orders will cause the records in the orders table to be orphaned. If you have integrity and relationships set on your tables, SQL returns an error if you try to delete records with linked records. 

The following SQL statement deletes the customer record with the ID 123. 

DELETE FROM Customer

WHERE CustomerId = 123 

The result is your table now looks like the following. 

CustomerId	First_name	Last_name	City	State
321	Frank	Doe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX
459	Ed	Thompson	Atlanta Interested in learning more? Why not take an online SQL course?	GA

If you remember from the UPDATE chapter, you can use a SELECT statement in your WHERE clause. The same is true for the DELETE statement. For instance, suppose you have a separate table that contains duplicate customer records. You can see from the above table that 355, 456, and 459 are all duplicate records. You have a table that contains 456 and 459 to indicate that these are duplicate records. Of course, when you work with large database tables, you won't be able to skim a table and identify duplicate records. You rely on the external table that you have to delete records using the same type of SELECT statement that you used in the UPDATE statement. 

In this example, the external table looks like the following data set. We'll name the table "DeleteCustomer." 

CustomerId	First_name	Last_name	City	State
456	Ed	Thompson	Atlanta	GA
459	Ed	Thompson	Atlanta	GA

The following DELETE statement uses the CustomerId from this table and uses it to delete the main customer table. 

DELETE FROM Customer

WHERE CustomerId IN (select CustomerId from DeleteCustomer) 

The above SQL statement works in the same way that the UPDATE statement worked when we used a sub-SELECT except with this statement records are deleted and not UPDATED. With SQL, the sub-SELECT statement is executed first. The sub-SELECT statement gets a list of Customer IDs and passes them to the main DELETE statement. Notice that the WHERE clause has an IN statement and not an equal sign. The IN clause tells SQL to manipulate records within a set of records determined by the SELECT statement. 

The DELETE statement's WHERE clause looks up all customer IDs and deletes them based on matches. In this example, there are two matching customers. The statement deletes the records, and your result is the following data set. 

CustomerId	First_name	Last_name	City	State
321	Frank	Doe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX

Let's go back to the original table and assume that you only want to delete a select set of records in the DeleteCustomer table. In some instances, you'll only want to delete a set of records from an external table that match a certain criteria. The following DELETE statement deletes only one record from the main customer table. 

DELETE FROM Customer

WHERE CustomerId IN (select CustomerId from DeleteCustomer WHERE CustomerId = 459) 

The result is your data now looks like the following. 

CustomerId	First_name	Last_name	City	State
321	Frank	Doe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX

Notice that only one record was deleted instead of an entire table of records.

The DELETE statement also lets you use multiple conditions in the WHERE clause. Most programmers delete records based on multiple parameters to ensure that only the right records are removed. Since deleting the wrong record can cause major issues and bugs in an application, developers use multiple WHERE clause parameters to limit the number of records deleted.

For instance, suppose you know that you want to delete a customer with the name "Ed Thompson" but you don't know the CustomerId. You know his customer ID is less than 456. In this example, the customer ID is 455, but you could have thousands of customer records and won't be able to identify the customer's ID by looking at the data set.

The following SQL statement removes records based on two parameters in a WHERE clause.

DELETE FROM Customer

WHERE first_name = ‘Ed' and last_name='Thompson' and CustomerId < 456 

In the above example, three parameters are set. The first two identify the name, but you have multiple "Ed Thompson" customers in your database. If you only used those two parameters, both customer accounts would be deleted. Adding the CustomerID < 456 clause tells SQL to only delete customer with an ID less than 456. In the example data set, you only have 1 record that matches the WHERE clause, so SQL deletes only one record. Your SQL software should tell you that one record was affected.

Your Customer table now looks like the following. 

CustomerId	First_name	Last_name	City	State
321	Frank	Doe	Dallas	TX
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX

Now, you have one customer with the name Ed Thompson. 

One tip when using the DELETE statement and your WHERE clause. Suppose you create your DELETE statement but you aren't sure what records will be deleted. Since you know that deleting records can harm your data integrity, what can you do? The answer is to run your WHERE clause as a SELECT statement first. 

Let's go back to the data set that had two Ed Thompson customers in the table. Your current table looks like the following. 

CustomerId	First_name	Last_name	City	State
321	Frank	Doe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX

You again want to delete only customers named Ed Thompson that have an ID less than 456, but you don't know if you'll delete one, two or thousands of records. The answer is to write a SELECT statement with the exact WHERE clause parameters as your DELETE statement. The SELECT statement will tell you what records will be deleted, so you ensure that you remove only the right records. 

For instance, the following SELECT statement matches the last DELETE statement we wrote earlier. 

SELECT * FROM Customer

WHERE first_name = ‘Ed' and last_name='Thompson' and CustomerId < 456 

The SQL database will then show you the following record set. 

CustomerId	First_name	Last_name	City	State
455	Ed	Thompson	Atlanta	GA

Using the SELECT statement first, you can review the results to ensure that the data you will delete is correct. Of course, if you have thousands of records, you can't go through all of them. Spot check your record set and verify that the data is correct. Using a SELECT statement before you perform a DELETE will stop you from making mistakes. 

Now, you can use your WHERE clause in your DELETE statement. You know exactly the number of records that will be deleted. Running the following DELETE query removes the 1 record returned from the SELECT statement. 

DELETE FROM Customer

WHERE first_name = ‘Ed' and last_name='Thompson' and CustomerId < 456 

Notice that the WHERE clauses are the same, so you've added verification before you delete records. 

There is one last way to delete all records from a table. For example, you might have a temporary table that you want cleared each time you import records. The DELETE statement could be used, but SQL offers a TRUNCATE statement that deletes all records from a table. Use the TRUNCATE statement carefully, though. DELETE statements can be rolled back in most database systems, while TRUNCATE deletes records permanently. The only way to recover from a TRUNCATE statement is restore from a backup.