SQL DELETE Statement
The SQL DELETE statement is used to delete or remove the existing records in a specific table.
The SQL DELETE statement is also used to delete all records (without SQL WHERE clause) or specific records that match a specified criteria using SQL WHERE clause. This is accomplished by carefully constructing a where clause.
Related Links
SQL DELETE Syntax
The basic syntax of SQL DELETE statement with SQL WHERE clause is as follows:
DELETE FROM table_name1
WHERE some_column_name = some_value_1;
You can combine N number of conditions using AND or OR operators.
You do not need to list column names in the SQL DELETE statement since you are removing the entire record from the table.
Note: The SQL WHERE clause in the SQL DELETE statement!
The SQL WHERE clause specifies criteria to delete, which row or rows that should be deleted. If you omit the SQL WHERE clause, all rows will be removed!
Sample Database Table - Books
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | The Complete Guide to SQL Server | 155 | 2009 | Performance | Dharan |
2 | Making Sense Of SQL | 199.97 | 2006 | Programming | Padmavathi |
3 | Understanding the New SQL | 110 | 2007 | Programming | Varshini Kutty |
4 | Sql Server Concurrency | 205 | 2007 | Programming | Nirmala |
Sample Database Table Structure - Books
Column Name | Data Type |
---|---|
BookID | Number |
BookName | Text |
BookPrice | Number |
RelYear | Number |
DomainName | Text |
AuthorName | Text |
Text Column Vs Numeric Column
SQL requires single or double(most database support) quotes around text or string values. However, we write numeric fields without quotes:
SQL DELETE Example
Example 1
The following SQL statement will delete a existing row from column "BookID = 7", in the "Books" table:
DELETE FROM Books
WHERE
BookID = 3;
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | The Complete Guide to SQL Server | 155 | 2009 | Performance | Dharan |
2 | Making Sense Of SQL | 199.97 | 2006 | Programming | Padmavathi |
4 | Sql Server Concurrency | 205 | 2007 | Programming | Nirmala |
Example 2
The following SQL statement will delete multiple existing row(s) from column BookPrice less than "200" and DomainName = "Programming", in the "Books" table:
DELETE FROM Books
WHERE
BookPrice < 200 AND DomainName = 'Programming';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | The Complete Guide to SQL Server | 155 | 2009 | Performance | Dharan |
4 | Sql Server Concurrency | 205 | 2007 | Programming | Nirmala |
Example 3
The following SQL statement will delete all existing rows from the "Books" table:
DELETE FROM Books;
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|
Related Links