When working with databases like Oracle, DB2 and SQL Server, you will at times need to remove data from a table. Using SQL or Structured Query Language, you can do just that with the DELETE FROM sql statement. This an easy yet powerful SQL statement so you will want to handle it with care.

Syntax of Delete From SQL Command

Here is the basic syntax for DELETE FROM SQL command.

DELETE FROM table name| view name [WHERE] condition:

Where:

Table name: This is the table you are trying to remove data from

View name: This is the view you are trying to remove data from

[WHERE]: Optional parameter which will limit the number of records that are deleted. Please note that if you do not use this clause, ALL the rows in the SQL table will be deleted!

Examples of Delete From SQL Command

For today’s discussion, let us go ahead and look at Northwind sample database. We will use SQL Server 2008 to demonstrate SQL Delete command. If you are new to SQL Server, we suggest an excellent site on SQL Tutorials. We will use New_Customers table with information like Customer ID, Contact information and Customer address data. In order to first pull data from our table, we can use a simple SELECT statement as follows:

SELECT *

  FROM New_Customers

WHERE Country=’UK’

BTW all the SQL Statements are included for download under Related Links.

This will display only those customers that reside in United Kingdom. In SQL Server, you can use Management Studio to execute SQL statements. After you type it in the query window, you can click on the Execute button (Red Exclamation Point). We have included a screen shot of what DELETE FROM statement looks like on our SQL Server:

delete-from-sql1

 

How do I delete specific records in a table?

Looking at our data, let’s say we wanted to delete the one customer in City of Cowes, they no longer do business with us. How would we do that? We can write the Delete SQL query as follows:

DELETE
  FROM New_Customers
WHERE City=’Cowes’.

Notice we placed the City name is single quotes as this text data. When we executed this in SQL server 2008 Management Studio, we got the following confirmation (lower pane):

delete-from-sql-statement2

 

Next we would like to check if the record was indeed deleted. We execute the original SELECT SQL statement again. When we did that, we actually saw 6 records instead of 7 that we had originally seen.

Here is what it looks like on our computer monitor:

delete-from-sql-query3

 

How about we take this a step further and see if we can remove data rows in a table using multiple fields. What about getting rid of all the customers that have a Contact Title of Sales Representative and are in City of London and Country of UK.

Here’s what the DELETE SQL command for this looks like:

DELETE

  FROM New_Customers

WHERE

ContactTitle=’Sales Representative’

and City=’London’

and Country=’UK

When we executed this in SQL Server Management Studio, we got the following result set:

delete-from-sql-server4

 

As a last check, we run the following SQL SELECT statement to see what else is left from our Customers in UK. We run the following statement:

SELECT *
  FROM New_Customers
WHERE Country=’UK’

As you can see below, we only have 3 customers left which is what we expected.

delete-from-sql-table5

 

This concludes the lesson on Delete From SQL Statement

 

Related links on DELETE SQL Command: