Welcome to Lesson 3 of the TSQL Tutorial. In this lesson we will cover delete statements. I hope you enjoy the lesson.
Select Statements
DELETE
The DELETE
statement is used to delete rows from a table.
Usage:
DELETE
FROM table
WHERE criteria
Example: We want to delete record(s) where the first name equals Robert.
(Note: When using a delete statement, it is very important to limit the
scope by using a where clause. If no where clause is used, all the records
records will be deleted.)
(This is the employees table before we run the delete statement.)
|
EmployeeID
|
LastName
|
FirstName
|
Title
|
BirthDate
|
HireDate
|
Address
|
City
|
|
1
|
Davolio
|
Nancy
|
Sales
Representative
|
12/8/1948
|
5/1/1992
|
507 - 20th
Ave. E.
|
Seattle
|
|
2
|
Fuller
|
Andrew
|
Vice
President, Sales
|
2/19/1952
|
8/14/1992
|
908 W.
Capital Way
|
Tacoma
|
|
3
|
Leverling
|
Janet
|
Sales
Representative
|
8/30/1963
|
4/1/1992
|
722 Moss
Bay Blvd.
|
Kirkland
|
|
4
|
Peacock
|
Margaret
|
Sales
Representative
|
9/19/1937
|
5/3/1993
|
4110 Old
Redmond Rd.
|
Redmond
|
|
5
|
Buchanan
|
Steven
|
Sales
Manager
|
3/4/1955
|
10/17/1993
|
14 Garrett
Hill
|
London
|
|
6
|
Suyama
|
Michael
|
Sales
Representative
|
7/2/1963
|
10/17/1993
|
123 Miner
Rd.
|
London
|
|
7
|
King
|
Robert
|
Sales
Representative
|
5/29/1960
|
1/2/1994
|
Edgeham
Hollow
|
London
|
|
8
|
Callahan
|
Laura
|
Inside
Sales Coordinator
|
1/9/1958
|
3/5/1994
|
4726 -
11th Ave. N.E.
|
Seattle
|
|
9
|
Dodsworth
|
Anne
|
Sales
Representative
|
1/27/1966
|
11/15/1994
|
7
Houndstooth Rd.
|
London
|
(Now we will run our delete statement below.)
DELETE
FROM employees
WHERE FirstName = 'Robert'
Results:
(1 row(s) affected)
(This is the employees table after we run the delete statement. Note that EmployeeID 7
Robert King is no longer there.)
|
EmployeeID
|
LastName
|
FirstName
|
Title
|
BirthDate
|
HireDate
|
Address
|
City
|
|
1
|
Davolio
|
Nancy
|
Sales
Representative
|
12/8/1948
|
5/1/1992
|
507 - 20th
Ave. E.
|
Seattle
|
|
2
|
Fuller
|
Andrew
|
Vice
President, Sales
|
2/19/1952
|
8/14/1992
|
908 W.
Capital Way
|
Tacoma
|
|
3
|
Leverling
|
Janet
|
Sales
Representative
|
8/30/1963
|
4/1/1992
|
722 Moss
Bay Blvd.
|
Kirkland
|
|
4
|
Peacock
|
Margaret
|
Sales
Representative
|
9/19/1937
|
5/3/1993
|
4110 Old
Redmond Rd.
|
Redmond
|
|
5
|
Buchanan
|
Steven
|
Sales
Manager
|
3/4/1955
|
10/17/1993
|
14 Garrett
Hill
|
London
|
|
6
|
Suyama
|
Michael
|
Sales
Representative
|
7/2/1963
|
10/17/1993
|
123 Miner
Rd.
|
London
|
|
8
|
Callahan
|
Laura
|
Inside
Sales Coordinator
|
1/9/1958
|
3/5/1994
|
4726 -
11th Ave. N.E.
|
Seattle
|
|
9
|
Dodsworth
|
Anne
|
Sales
Representative
|
1/27/1966
|
11/15/1994
|
7
Houndstooth Rd.
|
London
|