SQL Server DELETE – Removing One or More Rows from a Table with Slightly Advanced Scenarios

The current article focuses on slightly advanced use of the DELETE statement to remove one or more records (rows) from a table. While it targets beginners mostly, its tips can be of help to all database specialists.

Prerequisites: the DELETE statement Basic Scenarios

If you are not already familiar with the basic uses of the Delete statement, we’d recommend you reviewing the previous article SQL Server DELETE – Removing One or More Rows from a Table. That article focuses on the following aspects:

1. Setting up a sample database.
2. Running SQL scripts against the sample database.
3. Creating a table in a sample database and inserting data into it.
4. Deleting all rows from a table.
5. Deleting one or more rows from a table based on a condition.
6. Deleting one or more rows from a table based on multiple conditions.

These data are crucial to grasp before we jump to the slightly advanced use of the Delete statement, so, review that article if you don’t have the necessary background so far.

Slightly Advanced Scenarios for the DELETE statement

As with any other walkthrough, we must first set up a sample database to test running our scripts against it safely.

A tip about the sample database setup

I would highly recommend installing the SQL Server Developer Edition on your machine locally first. It is better for learning and testing purposes.

SQL Server developer edition download

Setup a Sample Database (WatchesDelSample)

We are going to set up a database called WatchesDelSample. This database contains the following three tables:

1. Watch.
2. Colour.
3. WatchType.

The Watch table is the main one. It contains the Name, Colour, and Type of the watch. The information about both type and color comes from the two referencing tables Color and WatchType connected via foreign keys.

Setup the sample database with the following script:

-- Create sample database WatchesDelSample
USE MASTER
GO

CREATE DATABASE WatchesDelSample
GO

USE WatchesDelSample

-- Creating a reference table WatchType
CREATE TABLE dbo.WatchType
(
	WatchTypeId INT IDENTITY(1,1),
	Name VARCHAR(50)NOT NULL,
	Detail VARCHAR(200)NULL 
	CONSTRAINT PK_WatchType_WatchTypeId PRIMARY KEY (WatchTypeId)
)
GO

-- Populating (adding rows to the) table WatchType
SET IDENTITY_INSERT dbo.WatchType ON
GO
INSERT INTO dbo.WatchType
(
  WatchTypeId
 ,Name
 ,Detail
)
VALUES
(
  1  -- ID - INT Primary Key
 ,'Analogue' -- Name - varchar(50) NOT NULL
 ,'This is Analogue' -- Detail - varchar(200)
),
(
  2  -- ID - INT Primary Key
 ,'Digital' -- Name - varchar(50) NOT NULL
 ,'This is Digital' -- Detail - varchar(200)
),
(
  3  -- ID - INT Primary Key
 ,'Sports' -- Name - varchar(50) NOT NULL
 ,'This is Sports' -- Detail - varchar(200)
);
GO
SET IDENTITY_INSERT dbo.WatchType OFF
GO

-- Creating a reference table Color
CREATE TABLE dbo.Color
(
	ColorId INT IDENTITY(1,1),
	Name VARCHAR(50)NOT NULL,
	Detail VARCHAR(200)NULL 
	CONSTRAINT PK_Color_ColorId PRIMARY KEY (ColorId)
)
GO

-- Populating (adding rows to the) table Color
SET IDENTITY_INSERT dbo.Color ON
GO
INSERT INTO dbo.Color
(
  ColorId
 ,Name
 ,Detail
)
VALUES
(
  1  -- ID - INT Primary Key
 ,'Black' -- Name - varchar(50) NOT NULL
 ,'This is Black' -- Detail - varchar(200)
),
(
  2  -- ID - INT Primary Key
 ,'White' -- Name - varchar(50) NOT NULL
 ,'This is White' -- Detail - varchar(200)
),
(
  3  -- ID - INT Primary Key
 ,'Blue' -- Name - varchar(50) NOT NULL
 ,'This is Blue' -- Detail - varchar(200)
);
GO
SET IDENTITY_INSERT dbo.Color OFF
GO

-- Creating a table Watch
CREATE TABLE dbo.Watch
(
	WatchId INT IDENTITY(1,1),
	Name VARCHAR(50),
	WatchTypeId INT,
	ColorId INT,
	Price DECIMAL(5,2),		
	CONSTRAINT PK_Watch_WatchId PRIMARY KEY (WatchId)
)
GO

-- Creating foreign key constraint on Watch table to get WatchTypeId values from WatchType table
ALTER TABLE dbo.Watch
    ADD CONSTRAINT [FK_Watch_WatchType_WatchTypeId] 
FOREIGN KEY ([WatchTypeId]) REFERENCES dbo.[WatchType] ([WatchTypeId]);

-- Creating foreign key constraint on Watch table to get ColorId values from Color table
ALTER TABLE dbo.Watch
    ADD CONSTRAINT [FK_Watch_Color_ColorId] 
FOREIGN KEY ([ColorId]) REFERENCES dbo.[Color] ([ColorId]);


-- Populating (adding rows to the) table Watch getting some columns values from reference tables
SET IDENTITY_INSERT WatchesDelSample.dbo.Watch ON
GO
INSERT WatchesDelSample.dbo.Watch(WatchId, Name, WatchTypeId, ColorId, Price) VALUES (1, 'Casio', 1, 1, 100.00)
INSERT WatchesDelSample.dbo.Watch(WatchId, Name, WatchTypeId, ColorId, Price) VALUES (2, 'Timex', 2, 2, 70.00)
GO
SET IDENTITY_INSERT WatchesDelSample.dbo.Watch OFF
GO

Quick Data Check

Let us view all the rows of the Watch table. For that, run the following script:

-- View the watch table data (rows)
SELECT w.WatchId
      ,w.Name
      ,w.WatchTypeId
      ,w.ColorId
      ,w.Price FROM dbo.Watch w

The output is as follows:

Note that I am using dbForge Studio for SQL Server for this demo. However, you can use the SQL Server Management Studio (SSMS) for running the same scripts – the results will be the same.

Understanding the IDs (codes) behind the Type and Color Columns

As you can see, there are some IDs under the following columns of the Watch table:

1. WatchTypeId
2. ColorId

These columns get values from the reference tables where they are originally defined. The Watch table connects to those reference tables via foreign key constraints.

Three problems occur with the above output:

1. We can see the WatchTypeId and ColorId but do not understand what they are.
2. If we understand what these ids mean, we must go back to their original tables all the time to check.
3. Most importantly, why do we need to get Color and Type from other tables?

There is a reason why we defined Color (ColorId) and Type (WatchTypeId) in other tables. We need to ensure that these values remain consistent.

If we don’t originally define the value in the reference tables, we could have mixed the words to represent the color or type. For instance, there could be both Blue and Blues, or Analog and Analogue. To avoid such a problem, we standardize colors and types in reference tables. Then we pass on their codes to the main table.

By joining the Watch table with other reference tables, we can retrieve the values behind these codes. It is a common practice in database development.

Viewing the Watch table with Type and Color values behind IDs

We can view the actual meaning of the codes behind Color and Type by executing the following script:

-- View the watch table data (rows)
SELECT w.WatchId
      ,w.Name
      ,wt.Name AS WatchType
      ,c.Name AS ColorName
      ,w.Price FROM dbo.Watch w
  INNER JOIN dbo.Color c ON c.ColorId=w.ColorId
  INNER JOIN dbo.WatchType wt ON w.WatchTypeId = wt.WatchTypeId

The result is as follows:

Understanding the Database Architecture

We need more information about the architecture of this database. Our focus is on how tables link to each other.

The above image demonstrates the scenario of the reference tables helping the main table to receive consistent data. It is not a simple scenario especially for beginners, although it is common in many databases.

We are studying this architecture because we need to understand how to delete one or more rows from any of the above tables when they are linked like this.

Deleting a row from the reference (Color) table

Can we delete a row from the reference table or not? Let us find out the answer.

We delete the first row from the Color table:

-- Deleting one row with color id 1 from the reference table color 
DELETE FROM Color
WHERE ColorId = 1

The result is as follows:

The error means it is not permitted to delete that row that we wanted.

In other words, we cannot delete a row from a table that is being referenced by another table.

Linked Rows vs Unlinked Rows

Let’s divide the rows of a reference table into the following two categories:

1. Linked rows.
2. Unlinked rows.

A linked row is a row of a reference table that is used by another table. An unlinked row is a row of a reference table that another table does not appeal to.

We can delete unlinked rows (records) of a reference table straight away.

Our earlier attempt of deleting a row from the Color table failed because that ColorId (1) was in the use of the main Watch table.

View the Reference Table (Color)

Let us view the reference table as follows:

-- View reference table Color
SELECT c.ColorId
      ,c.Name
      ,c.Detail FROM dbo.Color c

The result set is below:

From the previous outputs, we know that the color Blue (ColorId: 3) is not in use by the Watch table since there is no blue watch stored in the table yet.

Deleting an unlinked row from the reference (Color) table

Execute the following script:

-- Deleting unlinked row with color id 3 from reference table Color
DELETE FROM Color
WHERE ColorId = 3 -- blue color

We have deleted the row successfully, and we can confirm it by viewing the table:

--View reference table Color after deleting the unlinked row
SELECT c.ColorId
      ,c.Name
      ,c.Detail FROM dbo.Color c

The result set is below:

It shows that the row containing the blue color has been successfully removed from the table.

A tip about the data removal from the reference table

Remember that you cannot delete a record (row) from a reference table if it is in use of another table or group of tables. However, you can delete a record from the same (reference) table if it is not in use.

Deleting a linked row from the reference (Color) table

What if we want to remove a row from a reference table knowing that it passes the reference data such as colors to another table? In other words, how do we delete a linked row from the reference table?

First, we must delete that row from the main table where it is referenced.

For instance, we can delete the color White from the Color table as follows:

1. Delete all rows from the main table (Watch) where the color is White (based on id).
2. Delete the row from the reference Color table where the color is White (based on id).

Now, let’s examine it in practice.

Deleting all rows where color is White from the main (Watch) table

Our goal is to remove the traces of the white color from both reference and main tables.

Let’s have a look at the data before deleting it. We want to check how many rows from the main table contain the color id 2 (white):

-- View Watch table before deleting rows with white color (color id:2)
 SELECT w.WatchId
       ,w.Name
       ,w.WatchTypeId
       ,w.ColorId
       ,w.Price FROM dbo.Watch w
  WHERE w.ColorId=2 -- White Color

The result is below:

Now, delete the rows having color id 2 by running the following T-SQL script:

-- Deleting all the rows with color id 2 from main table Watch
DELETE FROM dbo.Watch
WHERE ColorId = 2 -- white color 

The output is as follows:

View the main table after removing all rows with the white color

We need to check the main table for any rows containing color id 2:

-- View the watch table data (rows)
SELECT w.WatchId
      ,w.Name
      ,wt.Name AS WatchType
      ,w.ColorId 
      ,c.Name AS ColorName
      ,w.Price FROM dbo.Watch w
  INNER JOIN dbo.Color c ON c.ColorId=w.ColorId
  INNER JOIN dbo.WatchType wt ON w.WatchTypeId = wt.WatchTypeId

As we see, the records for the white color watches are absent. It proves that we have successfully deleted all those rows.

Deleting a previously linked row from the reference table (Color)

After we remove the referenced rows from the main table, we can also remove the previously linked row from the reference table. The fact is, that link now is not there anymore.

Run the following script against the reference Color table to delete the row with the color id 2 (white):

-- View reference table before removing color id 1 (white)
SELECT c.ColorId
      ,c.Name
      ,c.Detail FROM dbo.Color c

-- Deleting one row with color id 2 from the reference table color 
DELETE FROM Color
WHERE ColorId = 2 -- White Color

  -- View reference table after removing color id 1 (white)
SELECT c.ColorId
      ,c.Name
      ,c.Detail FROM dbo.Color c

The output is as follows:

Congratulations!

We’ve learned how to remove one or more rows from a reference table. We can do it both if the row is being referenced and if not. Also, we’ve examined deleting rows from the main table.

A tip about deleting all data

There is another T-SQL statement known as Truncate Table – it is more efficient for removing all data from a table. However, the table must not be referenced elsewhere because then you have to delete the data from the main table first. It is the same as we demonstrated in this article earlier. Then, we’d apply the Truncate statement against the reference table as a final step.

The code is as follows:

-- Deleting all rows from the main table using Truncate 
  TRUNCATE TABLE dbo.Watch

However, just like with the Delete statement, you have to be very careful with Truncate, or you end up deleting all the data from a table.

Word of Advice

Deletion of rows in real-time scenarios mostly helps us either to remove unwanted data (such as retired models) from the main database or to archive data and store it in an archive database.

Things to do

Now that you can delete one or more rows in slightly advanced scenarios such as linked tables, try the following things to improve your skills further:

1. Delete the Analog watch type from the reference table WatchType based on the ID.
2. Delete all rows from the Color reference table.
3. Try resetting the sample database and then see how fast you can delete all data from all (reference and main) tables.

Tags: sql delete, sql server Last modified: September 17, 2021