SQL Server delete statement: How to delete a row or all rows from the table (2023)

Correct use of the DELETE statement to delete data is critical and presents many problems. However, there are standard practices for using the DELETE statement that simplify all of these tasks.

This article looks at some of the working life scenarios to give you the most useful tips for using the DELETE statement correctly. You can remove data from a table in several ways. discover theDifference between DELETE and TRUNCATE in SQL Servercovered with practical examples.

T-SQLextinguishdomainthe essential

First, let's get acquainted with the T-SQL delete statement in the simplest possible way.

The delete statement, as the name suggests, is a statement that helps us delete data from the database table.

a tableIt is a structure that you create in a database to store your data. For example, we may have a books table to store records of those books.

a data baseit is an organized collection of data and the data structures to store that data. In other words, the data can be stored within the database in the form of tables.

How to delete a row or multiple rows

The delete statement allows us to delete one or more records (commonly called rows) from a table.

The delete statement removes some or all of the data (rows) from a table.

According to Microsoft documentation, the delete statement deletes one or more rows from a table or view in SQL Server.

One might wonder how the statement defines whether to remove some or all of the data (rows) from a table. The answer lies in the criteria or conditions that specify what must be removed.

Delete command in SQL Server

The simplest syntax for the declaration is as follows:

Exclude <TableName> WHERE <Condition>

You must specify the name of the table and the criteria/conditions to remove data (rows) from the table.

Note: It is important to use the DELETE statement with a condition (WHERE clause), although the conditional requirement is not required.

If you run the DELETE table command without the WHERE condition, you will end up deleting all rows (data) from the table. So make it a habit to use the WHERE condition unless you want to delete all rows.

compatibility

This statement is supported by many versions of SQL Server, including the following:

1. SQL Server 2012 and later versions.
2. Cloud-based SQL Server database (Azure SQL Database).
3. Cloudbasiertes SQL data storage (Azure Synapse Analytics).

Step-by-step checklist for deleting table rows

Now let's examine the use of the Delete statement through several practical scenarios.

The summary of the steps.

1. Set up a sample database.
2. See the dates.
3. Delete data.
4. Add more data back to the table.
5. Preview the data before deleting it.
6. How to exclude data in a column based on a condition.
7. Data preview after deletion.
8. Add more data back to the table.
9. Preview the data before deleting it.
10. Delete data based on another condition.
11. Data preview after deletion.
12. Paste the data back into the table.
13. Clear the data this time based on two conditions.
14. Data preview after deletion.

Configuration sample database (BooksSample)

We need a sample database to test and run the scripts. First we need to set up this example database which covers the following steps:

1. Create a sample database.
2. Create a table in the sample database.
3. Insert the data (two rows) into the database table.

Abra SQL Server Management Studio odbForge Studio para SQL Serverand run the following script to set up the sample database:

-- Connect to the 'master' database to run this snippet BooksSample - create table bookCREATE TABLE [dbo].[Book]( [BookNumber] INT NOT NULL PRIMARY KEY,-- primary key column [Title] VARCHAR( 150) NOT NULL, [Stock] SMALLINT NOT NULL );GO-- Insert rows into table 'Book' INSERT INTO [dbo].[Book] ( -- Columns to insert data into [Book Number], [Title] , [Stock] )VALUES ( -- First line: values ​​for the columns of the list 1 above, 'Learn SQL in 7 days', 50), ( -- Second line: values ​​for the columns of the list 2 above, ' Creation of databases in minutes' , 50);IR

View the data (table of books)

Let's look at the newly created and populated table in the database. Run the following script:

-- Display data (rows) from table (book) SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The output is:

We can see the two rows of the table (book). At the moment these are all the data that exist in this table.

delete the data

As we remember, if we forget to mention the condition/criteria for correct deletion, there is a risk of deleting all rows from a table.

Always use the WHERE clause with the delete statement to avoid accidental data loss. The only exception should be the case where you need to delete all data on purpose.

To remove all data (rows) from the sample database table, run the following script:

-- Delete all data (rows) from the BookDELETE FROM dbo.Book table

The output is:

Show data after deletion

Now we need to check if all rows are deleted:

-- Display data (rows) from table (book) SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The results are:

In this way we have successfully removed all rows fromthe bookTable. To do this, we apply the DELETE statement without any exclusion criteria/conditions.

Paste the data back into the table (with the same title)

We can insert the data (rows) back into the table and apply the DELETE statement based on some conditions/criteria.

This time we decided to include more lines, but with the same title on purpose:

-- Insert rows into table 'Book' INSERT INTO [dbo].[Book] ( -- Columns to insert data into [BookNumber], [Title], [Stock] )VALUES ( -- First row: values ​​for the columns from list 1 above, 'Learn SQL in 7 days', 50), ( -- Second row: values ​​for columns from list 2 above, 'Create databases in minutes', 50), ( -- Third row : values ​​for the columns of the previous list 3, 'Database creation in minutes', 50), ( -- Fourth row: values ​​for the columns of the previous list 4, 'Database creation in minutes', fifty); GO

The output is the following:

supervision: To recover the deleted or changed data, you can use the special software solutions. EITHERdbForge transaction logThe solution allows you to recover that data and see who deleted or modified it and when.

Preview data before deleting

Run the following script to view the data:

-- Display data (rows) from table (book) SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The output is:

The output shows that we accidentally inserted three lines with the same book title. That is a problem. The simple solution is to remove unnecessary rows by applying the specific condition to remove rows with duplicate titles.

How to remove data in a SQL column based on a condition (BookNumber)

Important: We can consider one of the following ways to solve this problem:

1. Delete by book number
2. delete by title

In my scenario, I choose not to delete by title. If we delete by title, we end up deleting all rows that contain that title, including the ones we need to keep. Therefore, the recommended approach is to drop the table based onbook numberTo divide.

If we look at the result set, we can easily understand this.Book number: 3miBook number: 4they are duplicate lines. It has already been explained in detail before.How to remove duplicates in SQL. We need to remove them to keep the database consistent.

The following options appear again:

1. Delete where BookNumber is greater than 2.
2. Delete where BookNumber is 3 and 4.

Let's choose the first option. Note, however, that it is only valid if there are no rows after the known duplicate rows.

Run the following script:

-- Delete all data (rows) from the books table where BookNumber is greater than 2 DELETE FROM dbo.BookWHERE BookNumber>2

Show data after deletion

Let's check the table after removing the data:

-- Display data (rows) from table (book) SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The output is:

Add more data to the table (more inventory)

To insert more data (row) related to the stock, we use the following script:

-- Insert rows into table 'Book' INSERT INTO [dbo].[Book] ( -- Columns to insert data into [BookNumber], [Title], [Stock] )VALUES ( -- First row: values ​​for the columns from list 3 above, 'Basic Data Structures', 60), ( -- Second row: values ​​for columns from list 4 above, 'Extended Data Structures', 0)IR

View data before deleting

Take a look at the table:

-- Display data (rows) from table (book) SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The output is:

Delete data based on another condition

Suppose we need to delete ledgers to keep more accurate information in the database. To do this, we need to search for rows with Inventory is 0.

We can use DELETE statement with condition based on thatshareColumn value 0:

-- Deletes all out-of-stock books (rows) from the books table DELETE FROM dbo.BookWHERE Stock=0

Show data after deletion

-- Display data (rows) from table (book) SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

Add more data to the table (more bonds and stocks)

We add two more rows to the table:

-- Insert rows into table 'Book' INSERT INTO [dbo].[Book] ( -- Columns to insert data into [BookNumber], [Title], [Stock] )VALUES ( -- First row: values ​​for the columns in list 4 above, "Learn Azure SQL Database in 10 days", 0), ( -- Second row: values ​​for the columns in list 5 above, "Azure SQL Database concepts", 1) GO

Preview data before deleting

Check the rows before deleting anything else as per the requirement:

-- Display data (rows) from table (book) SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The output of the table is:

Clear data based on two conditions

This time we have to delete them all.Books(lines) where titles contain the wordsqland they are out of stock (their stock value is 0).

In other words, we remove all exhausted records related to SQL.

In this case, we need to specify more than one condition with the DELETE statement. We need to make sure that we only exclude books that are out of print and only books with the word SQL in the title.

Look at the following script:

-- Delete all out-of-stock books (rows) (where stock is 0) and associated SQL (title contains SQL) from the books table DELETE FROM dbo.BookWHERE Title LIKE '%SQL%' and Stock=0

Show data after deletion

Finally, we visualize the data:

important tip: Before deleting the data, execute the SELECT statement based on the same condition that you will use to delete. This ensures that your deletion activities are applied to the correct data.

For example, run the SELECT query first to make sure you get only the rows that need to be deleted:

SELECT * FROM dbo.BookWHERE Title LIKE '%SQL%' and Stock=0

Once you're sure, you can convert your SELECT statement to a DELETE statement:

DELETE FROM dbo.BookWHERE Title LIKE '%SQL%' and Stock=0

Cheers!

You have successfully passed the task of removing (deleting) one or more rows from a table according to the requirements.

Stay in touch for advanced delete scenarios and other pro tips on using the DELETE statement.

things to do

Now that you can successfully delete rows from a table, you can continue to train and improve your skills:

1. Try removing the rows where there is only one item in stock.
2. Delete all books where the title contains the wordStructure.
3. Delete all books (rows) except BookNumber 1.

DiscoverMore advanced scenarios in the SQL DELETE statement.

Sign:Delete SQL,Servidor-SQL,t-sql,t-sql statements Last Modified: September 17, 2021