Notes on SQL

Overview

One thing everybody should realise, is that SQL Server is a huge product – you’ll never learn all of it. Therefore, you shouldn’t feel embarrassed when you learn something that others around you might take for granted.

Reviewing somebody’s code, I came across a segment of the procedure that made no sense to me. As I knew the person who wrote the code is not generally prone to basic errors, I assumed the issue was with my understanding. Surely enough, I have discovered something that I found quite entertaining – you can update a table via the Common Table Expression (CTE) that is linked to it.

Test Environment

Very simple – one table with three simple entries.

Listing 1: Create a Test Database and a Test Table

CREATE DATABASE Test;
GO

USE Test;
GO

CREATE TABLE dbo.TestTable
(
    ID		INT IDENTITY,
	TestText	VARCHAR(50) NULL
);

GO

INSERT INTO dbo.TestTable
(
    TestText
)
VALUES
('First Entry'),
('Second Entry'),
('Third Entry');
GO

Listing 1: Initial Data

Updating via a CTE

First of all, use a CTE to extract the details from the table. This is all I have used CTEs for in the past – extracting the required data for further processing in subsequent queries.

Listing 2: CTE to Select Data

USE Test;
GO

WITH TestCTE
AS (SELECT ID,
           TestText
    FROM dbo.TestTable)
SELECT TestCTE.TestText
FROM TestCTE;
GO

Figure 2: Results from Listing 2

Update Data via a CTE

Now to actually alter a row of data through the CTE.

Listing 3: Change a Table’s Data and Select the Data

WITH TestCTE
AS (SELECT ID,
           TestText
    FROM dbo.TestTable)
UPDATE TestCTE
SET TestCTE.TestText = 'Second Entry Updated'
WHERE TestCTE.ID = 2;

SELECT ID,
       TestText
FROM dbo.TestTable;
GO

Looking at the contents of the table, the required row has been altered.

Figure 3: The Updated Data From Listing 3

Inserting a New Row via a CTE

As well as updating existing rows, it is also possible to add a row with the CTE.

Listing 4: Insert a Row and Select the Data

WITH TestCTE
AS (SELECT ID,
           TestText
    FROM dbo.TestTable)
INSERT INTO TestCTE
(
    TestText
)
VALUES
('Fourth Entry');

SELECT ID,
       TestText
FROM dbo.TestTable;
GO

Figure 4: The Updated Data From Listing 4

Deleting a Row via CTE

And if you can update and insert rows, it should be possible to delete them too.

Listing 5: Delete a Row and Select the Data

WITH TestCTE
AS (SELECT ID,
           TestText
    FROM dbo.TestTable)
DELETE TestCTE
WHERE TestCTE.ID = 1;

SELECT ID,
       TestText
FROM dbo.TestTable;
GO

And in the resultant query, the row with an ID of 1 is no longer there.

Figure 5: The Updated Data From Listing 5

Conclusion

I knew that you could manipulate rows via Views, but hadn’t realised that it was also possible via a CTE.

There is a never-ending range of things to learn from SQL Server. If you learn something that you believe is quite basic, don’t feel embarassed. There’s a very good chance that you know something about SQL Server that a lot of others don’t know.