SQL: Toddlers Query (DELETE & TRUNCATE Statement)

This is the last part of the DML Statement. DELETE statement is to remove a row or multiple rows in a table. Deleting the target table without the WHERE clause specifying your target column and value would mean a career without a job. J

Basic Syntax:

DELETE FROM table WHERE predicate;

Below are the sample DELETE statements that will help you remove records from your table.

Friends Table:

FriendsID	Friends_Number	FirstName	LastName	Country	City
1	7001	Mel	Passion	Philippines	Manila
2	7002	CJ	Samuel	Australia	Sydney
3	7003	Jasmin	Tyler	Philippines	Manila
4	7004	Fred	Dylan	Philippines	Novaliches
5	7005	Joseph	Jonathan	Philippines	Pasig
6	7006	Jennifer	Caleb	USA	Corona
7	7007	Champy	Rivera	New Zealand	Oakland
8	7008	Ivy	Hotdog	New Zealand	Oakland
9	7009	Sydney	Strips	Philippines	Malabon
10	7010	Miguel	Porkchop	Philippines	Mandaluyong

Example 1

OBJECTIVE: Run the command below to DELETE the rows WHERE country is filtered by Philippines.

DELETE FROM Friends WHERE Country = Philippines;

FriendsID	Friends_Number	FirstName	LastName	Country	City
2	7002	CJ	Samuel	Australia	Sydney
6	7006	Jennifer	Caleb	USA	Corona
7	7007	Champy	Rivera	New Zealand	Oakland
8	7008	Ivy	Hotdog	New Zealand	Oakland

NOTE: When you run this code, SQL Server returns the following message, indicating that 6 rows were deleted.

(6 row(s) affected)

Example 2

OBJECTIVE: Run the below TRUNCATE query to delete all rows in the target table.

TRUNCATE TABLE Friends;

FriendsID

Friends_Number

FirstName

LastName

Country

City

NOTE:

      -          After executing the query, the target table is empty.

-          TRUNCATE TABLE is substantially faster than DELETE and uses fewer system and transaction log resources and does not have optional filter (ex: WHERE).