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”.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>