SQL DELETE Statement

The SQL DELETE Statement is used to remove single record or multiple records from a database table.

SQL DELETE Statement Syntax

DELETE FROM TableName
WHERE conditions

If the WHERE clause is skipped, all the data in the table will be deleted!

SQL DELETE Statement Example

Table: Employees

EmployeeId	FirstName	LastName	Department	Salary
203	Mazojys	Fxoj	Finance	78000
204	Jozzh	Lnanyo	Finance	45800
205	Syllauu	Dfaafk	Finance	57000
206	Gecrrcc	Srlkrt	Finance	62000
302	Jssme	Bdnaa	Development	75000
303	Dnnaao	Errllov	Development	55000
304	Tyoysww	Osk	Development	49000

Example 1
Delete a record which EmployeeId is 206:

DELETE FROM Employees
WHERE EmployeeId=206

The Employees table will look like:

EmployeeId	FirstName	LastName	Department	Salary
203	Mazojys	Fxoj	Finance	78000
204	Jozzh	Lnanyo	Finance	45800
205	Syllauu	Dfaafk	Finance	57000
302	Jssme	Bdnaa	Development	75000
303	Dnnaao	Errllov	Development	55000
304	Tyoysww	Osk	Development	49000

Example 2
Delete rows from table Employees, if the value of Salary is greater than 70000:

DELETE FROM Employees
WHERE Salary > 70000

Now, the table Employees will look like:

EmployeeId	FirstName	LastName	Department	Salary
204	Jozzh	Lnanyo	Finance	45800
205	Syllauu	Dfaafk	Finance	57000
303	Dnnaao	Errllov	Development	55000
304	Tyoysww	Osk	Development	49000

Example 3
Delete all rows from the table Employees without delete the table itself:

DELETE FROM Employees

Or:

DELETE * FROM Employees

We can also use TRUNCATE to delete all rows from table:

TRUNCATE TABLE Employees

Warning

Be careful to use the DELETE statement, because you can not undo this statement.

Sometime, instead of using the DELETE statement to actually delete rows, you can use the UPDATE statement to mark a row as deleted.

For example, we add a column Status to the table Employees:

EmployeeId	FirstName	LastName	Department	Salary	Status
203	Mazojys	Fxoj	Finance	78000	Active
204	Jozzh	Lnanyo	Finance	45800	Active
205	Syllauu	Dfaafk	Finance	57000	Active
206	Gecrrcc	Srlkrt	Finance	62000	Active
302	Jssme	Bdnaa	Development	75000	Active
303	Dnnaao	Errllov	Development	55000	Active
304	Tyoysww	Osk	Development	49000	Active

If you want delete a record which EmployeeId is 206, you can use the UPDATE statement to mark its Status as “Deleted”:

UPDATE Employees
SET Status='Deleted'
WHERE EmployeeId=206

The table Employees will now look like:

EmployeeId	FirstName	LastName	Department	Salary	Status
203	Mazojys	Fxoj	Finance	78000	Active
204	Jozzh	Lnanyo	Finance	45800	Active
205	Syllauu	Dfaafk	Finance	57000	Active
206	Gecrrcc	Srlkrt	Finance	62000	Deleted
302	Jssme	Bdnaa	Development	75000	Active
303	Dnnaao	Errllov	Development	55000	Active
304	Tyoysww	Osk	Development	49000	Active

When you query the table, you can use the following statement:

SELECT *
FROM Employees
WHERE Status <> 'Deleted'

By this way, the rows which Status are “Deleted” will not included in the result-set, look like they are deleted for the end users. If we want to get these rows back sometime later, it will be very easy: simply update their status to “Active”.