SQL - DELETE Statement

Use the DELETE statement to delete records from the existing table in the current schema or tables of the schema on which you have the DELETE privilege.

Syntax:

DELETE FROM table_name [WHERE Condition];

This DELETE syntax is valid in all the databases such as SQL Server, Oracle, MySQL, PostgreSQL, SQLite, etc. The WHERE clause is optional.

For the demo purpose, the following Employee table in all the examples here.

EmpId FirstName LastName Email PhoneNo Salary
1 'John' 'King' '[email protected]' '650.127.1834' 33000
2 'James' 'Bond'
3 'Neena' 'Kochhar' '[email protected]' '123.456.4568' 17000
4 'Lex' 'De Haan' '[email protected]' '123.456.4569' 15000

You can delete the specific record(s) from the table using the WHERE clause. The following will delete a record from the Employee table where the value of EmpId is 4.

SQL Script: Delete Records
DELETE FROM Employee WHERE EmpId = 4;

Now, the Select * from Employee query will display the following rows.

EmpId FirstName LastName Email PhoneNo Salary
1 'John' 'King' '[email protected]' '650.127.1834' 33000
2 'James' 'Bond'
3 'Neena' 'Kochhar' '[email protected]' '123.456.4568' 17000

In the same way, the following will delete all employees from the Employee table whose Salary is more than 20000.

SQL Script: Delete Records
DELETE FROM Employee WHERE Salary > 20000;

Now, the Select * from Employee query will display the following rows.

EmpId FirstName LastName Email PhoneNo Salary
2 'James' 'Bond'
3 'Neena' 'Kochhar' '[email protected]' '123.456.4568' 17000

The following DELETE statement will delete all the records from the Employee table.

SQL Script: Delete All Rows
DELETE FROM Employee;

Now, the Select * from Employee query will display the empty table.

EmpId FirstName LastName Email PhoneNo Salary
           
Note:
You cannot delete the value of a single column using the DELETE statement. Use the UPDATE statement to set it NULL.