SQL DELETE Statement

SQL DELETE statement is used to permanently delete existing records (rows) from the table. This statement can be used to delete one or more records from the table. We can use where clause with the DELETE statement to delete specific records from table, however in the absence of where clause, the DELETE statement permanently removes all the records from the table.

DELETE statement Syntax

To remove specific rows from the table:

DELETE 
 FROM table_name 
WHERE Condition;

To remove all the rows from the table:

DELETE 
 FROM table_name;

This statement removes all the records from table but the table itself is not deleted, thus table structure, attributes and indexes remains intact.

SQL DELETE statement Example

Table: Customers

CustomerName   Amount   PurchaseYear   OrderNo
------------   ------   ------------   -------
Paul           1500          2018      1001
Rick           1600          2019      1100
Derek          800           2017      1301
Sheru          1100          2016      1789

Lets say we want to delete the records from the table, where the PurchaseYear is prior to the year 2018.
The following SQL statement deletes the specific records from the table, where the PurchaseYear is less than 2018.

DELETE FROM Customers WHERE PurchaseYear < 2018;

Results:
The Customers table after the execution of above DELETE statement, would look like this:

CustomerName   Amount   PurchaseYear   OrderNo
------------   ------   ------------   -------
Paul           1500          2018      1001
Rick           1600          2019      1100

Lets take some more examples:

Example: Delete the record of customer where CustomerName is ‘Rick’:

DELETE FROM Customers WHERE CustomerName = 'Rick';

This statement will delete 1 record from the table.

Example: Delete the records where Amount is greater than 1000:

DELETE FROM Customers WHERE Amount > 1000;

This statement will delete 3 records from the table.

Delete all records from the table

To delete all the records from the table, we use the DELETE statement without the where clause. For example: Lets say we want to delete all the records from the table “Customers”.

DELETE FROM Customers;

This statement deletes all the rows from table but the table itself is not deleted, thus table structure, table attributes and indexes remains intact.