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.
Leave a Reply