DELETE Statement (SQL Server) Simple and Advanced Examples

DELETE Statement (SQL Server) – Simple and Advanced examples / 07/03/2013

DELETE Statement (SQL Server) – Simple and Advanced examples

TheSQL DELETEstatement allows you to delete a single record or multiple records from a table.

The syntax for theSQL DELETEstatement is:

DELETE FROM table
WHERE predicates;

SQL DELETE Statement – One condition example

Let’s take a look at a simple example, where we just have one condition in ourSQL DELETEstatement:

DELETE FROM suppliers
WHERE supplier_name = ‘IBM’;

ThisSQL DELETEstatement would delete all records from the suppliers table where the supplier_name is IBM.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the followingSQL SELECTstatementbeforeperforming the delete.

SELECT count(*)
FROM suppliers
WHERE supplier_name = ‘IBM’;

SQL DELETE Statement – Using SQL EXISTS Clause example

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can’t list more than one table in theSQL FROMclause when you are performing a delete, you can use theSQL EXISTSclause.For example:

DELETE FROM suppliers
WHERE EXISTS
( select customers.name
from customers
where customers.customer_id = suppliers.supplier_id
and customers.customer_name = ‘IBM’ );

ThisSQL DELETEstatement would delete all records in the suppliers table where there is a record in the customers table whose name is IBM, and thecustomer_idis the same as thesupplier_id.

If you wish to determine the number ofrows that will be deleted, you can run the followingSQL SELECTstatementbeforeperforming the delete.

SELECT count(*) FROM suppliers
WHERE EXISTS
( select customers.name
from customers
where customers.customer_id = suppliers.supplier_id
and customers.customer_name = 'IBM' );

Frequently Asked Questions

Question:How would I write anSQL DELETEstatement to delete all records inTableAwhose data infield1field2DO NOTmatch the data infieldxfieldzofTableB?

Answer:You could try something like this for yourSQL DELETEstatement:

DELETE FROM TableA
WHERE NOT EXISTS
( select *
from TableB
where TableA.field1 = TableB.fieldx
and TableA.field2 = TableB.fieldz );

To delete rows from a table based on criteria in other tables

In this exercise, you will create a query that deletes rows from thejuveniletable that have matching rows in theadulttable of thelibrarydatabase.

After juvenile members are converted to adult members, those members must be deleted from the juvenile table.

USE library
DELETE FROM juvenile
FROM juvenile INNER JOIN adult
ON juvenile.member_no = adult.member_no

Specifying Conditions in the WHERE Clause

You also can use subqueries to determine which rows to delete from a table based on rows of another table. You can specify the conditions in theWHEREclause rather than using an additionalFROMclause.

Use a nested or correlatedsubqueryin theWHEREclause to determine which rows to delete.

USE Northwind
DELETE FROM Orders
WHERE CustomerID IN
(SELECT CustomerID
FROM Customers
WHERE Country = 'USA')

Data: 07/03/2013 Page:1

Total Chars: 2574Total Words: 497

File Size: 75 KB