{"id":528,"date":"2018-05-14T14:44:41","date_gmt":"2018-05-14T07:44:41","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=528"},"modified":"2020-04-11T20:13:28","modified_gmt":"2020-04-11T13:13:28","slug":"sql-server-delete","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-delete\/","title":{"rendered":"SQL Server DELETE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server DELETE<\/code> statement to remove one or more rows from a table.<\/p>\n\n\n\n

Introduction to SQL Server DELETE statement #<\/a><\/h2>\n\n\n\n

To remove one or more rows from a table completely, you use the DELETE<\/code> statement. The following illustrates its syntax:<\/p>\n\n\n

DELETE<\/span> [ TOP ( expression ) [ PERCENT<\/span> ] ]  \nFROM<\/span> table_name\n[WHERE<\/span> search_condition];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
First, you specify the name of the table from which the rows are to be deleted in the FROM<\/code> clause.<\/p>\n\n\n\n
For example, the following statement will delete all rows from the target_table<\/code>:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> target_table;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, to specify the number or percent of random rows that will be deleted, you use the TOP<\/code> clause.<\/p>\n\n\n\n
For example, the following DELETE<\/code> statement removes 10 random rows from the target_table<\/code>:<\/p>\n\n\n
DELETE<\/span> TOP 10<\/span> FROM<\/span> target_table;  \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because the table stores its rows in unspecified order, we do not know which rows will be deleted but we know for sure that the number of rows will be deleted is 10.<\/p>\n\n\n\n
Similarly, you can delete the 10 percent of random rows by using the following DELETE<\/code> statement:<\/p>\n\n\n
DELETE<\/span> TOP 10<\/span> PERCENT<\/span> FROM<\/span> target_table;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The rows that cause the search_condition<\/code> evaluates to true will be deleted.<\/p>\n\n\n\n
The WHERE<\/a><\/code> clause is optional. If you skip it, the DELETE<\/code> statement will remove all rows from the table.<\/p>\n\n\n\n
SQL Server DELETE statement examples #<\/a><\/h2>\n\n\n\n
Let’s create a new table for the demonstration.<\/p>\n\n\n\n
The following statement creates a table<\/a> named production.product_history<\/code> with the data copied from the production.products<\/code> table:<\/p>\n\n\n
SELECT<\/span> * \nINTO<\/span> production.product_history\nFROM<\/span>\n    production.products;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query returns all rows from the product_history<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> production.product_history;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
As can be seen clearly in the output, we have 321 rows in total.<\/p>\n\n\n\n
1) Delete the number of random rows example #<\/a><\/h3>\n\n\n\n
The following DELETE<\/code> statement removes 21 random rows from the product_history<\/code> table:<\/p>\n\n\n
DELETE<\/span> TOP (21<\/span>)\nFROM<\/span> production.product_history;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the message issued by the SQL Server:<\/p>\n\n\n
(21 rows affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It means that 21 rows have been deleted.<\/p>\n\n\n\n
2) Delete the percent of random rows example #<\/a><\/h3>\n\n\n\n
The following DELETE<\/code> statement removes 5 percent of random rows from the product_history<\/code> table:<\/p>\n\n\n
DELETE<\/span> TOP (5<\/span>) PERCENT<\/span>\nFROM<\/span> production.product_history;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server issued the following message indicating that 15 rows (300 x 5% = 15) have been deleted.<\/p>\n\n\n
(15 rows affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Delete some rows with a condition example #<\/a><\/h3>\n\n\n\n
The following DELETE<\/code> statement removes all products whose model year is 2017:<\/p>\n\n\n
DELETE<\/span>\nFROM<\/span>\n    production.product_history\nWHERE<\/span>\n    model_year = 2017<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output message:<\/p>\n\n\n
(75 rows affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
4) Delete all rows from a table example #<\/a><\/h3>\n\n\n\n
The following DELETE<\/code> statement removes all rows from the product_history<\/code> table:<\/p>\n\n\n
DELETE<\/span> FROM<\/span>  production.product_history;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that if you want to remove all rows from a big table, you should use the TRUNCATE TABLE<\/code> statement which is faster and more efficient.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server DELETE<\/code> statement to remove one or more rows from a table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Third, practically speaking, you will rarely remove all rows from a table but only one or several rows. In this case, you need to specify the search_condition<\/code> in the WHERE<\/a><\/code> clause to limit the number of rows that are deleted.<\/p>\n\n\n\n