SQL Delete Query
SQL Delete Query is used to remove rows from table in a database. In a database the storage and retrieval of data is the most important aspect. But, there are cases when we have insert some incorrect data by mistake and we have to remove it. Or the data is obsolete now and we can delete it, such as logging information that can be purged after few days.
Deletion of data is very important aspect of database maintenance. Deletion of unnecessary data is important in order to maintain a clean database with only valuable information stored as part of the table values.
SQL Delete Syntax
If we want to delete specific rows, then we need to provide delete statement with where clause.
|
1 2 3 |
DELETE From table_name WHERE condition; |
In the syntax above the deletion happens based on the condition that is specified in the WHERE clause.
SQL Delete Row
Let’s try to understand the DELETE command through some example. Let’s consider the following Customer Table to understand DELETE command.
| CustomerId | CustomerName | CustomerAge | CustomerGender |
|---|---|---|---|
| 1 | James | 32 | M |
| 2 | Diana | 26 | M |
| 3 | Annie | 35 | F |
We want to delete rows with CustomerGender as Female. The delete statement will be;
|
1 2 3 |
DELETE FROM Customer WHERE CustomerGender="F"; |
SQL Delete Single Row
For above table, result will be 1 record deleted. If you want to make sure that your SQL delete query will delete maximum one row, then always use primary key in the where clause.
|
1 2 3 |
DELETE FROM Customer WHERE CustomerID = 1; |
Above query will delete only one row or no rows if there is no customer with customer id as 1.
SQL Delete All Rows
If you will not provide where clause with delete statement, then whole table data will be deleted. Use this very carefully to avoid any unwanted data loss.
|
1 2 3 |
Delete From Customer; |
In the syntax above the deletion happens without any condition and will delete all the records of the table.
SQL Delete Table
If you want to delete the table itself, then you can use DROP statement like below.
|
1 2 3 |
Drop table Customer; |
Above command will delete Customer table with all it’s data.
Delete Statement Important Points
Delete Query in SQL should always be executed with the WHERE clause to avoid unwanted data loss. Delete statement without WHERE clause will delete all the records of the table and without proper rollback mechanism, your data could be lost forever.
Reference: For more information about DELETE please refer to Oracle Documentation.