Someone asked two interesting questions last night
1) Is there a way to construct an empty CTE?
2) Can a CTE be modified? Can I insert rows after it has been constructed?
Let’s look at question number one first. Is there a way to construct an empty CTE? Yes, there is, but why would you want to do this? Anyway, here is one way of having an empty CTE
WITH Hello
AS (
SELECT name
FROM sysobjects WHERE 1 =0
)
SELECT * FROM Hello
Question number two (Can a CTE be modified? Can I insert rows after it has been constructed?) is more interesting, first let’s look at a simple example and then we will look at something that makes more sense
We need to create a table first with 1 row
CREATE TABLE testNow(id int)
INSERT testNow VALUES(1)
Now run the following which will run an update statement against the CTE
;WITH Hello
AS (
SELECT id FROM testNow
)
UPDATE Hello SET id = 2
If we check the table now, we can see that the value changed from 1 to 2
SELECT * FROM testNow
What about inserts, can we insert into a common table expression? Sure, let’s take a look how we can do that.
;WITH Hello
AS (
SELECT id FROM testNow
)
INSERT Hello VALUES( 3 )
If we check the table now, we will see two rows in the table
SELECT * FROM testNow
So this is all, but what is the point? Really for the examples I gave there is none, you can just update the table directly instead. But, let’s look at a more interesting example
First we need to create the following table
CREATE TABLE SomeTable(id int,SomeVal char(1),SomeDate date)
INSERT SomeTable
SELECT null,'A','20110101'
UNION ALL
SELECT null,'A','20100101'
UNION ALL
SELECT null,'A','20090101'
UNION ALL
SELECT null,'B','20110101'
UNION ALL
SELECT null,'B','20100101'
UNION ALL
SELECT null,'C','20110101'
UNION ALL
SELECT null,'C','20100101'
UNION ALL
SELECT null,'C','20090101'
Now run a simple select statement
SELECT * FROM SomeTable
Here is what is in that table
id SomeVal SomeDate NULL A 2011-01-01 NULL A 2010-01-01 NULL A 2009-01-01 NULL B 2011-01-01 NULL B 2010-01-01 NULL C 2011-01-01 NULL C 2010-01-01 NULL C 2009-01-01
Now I would like to set the id to 1 for each distinct SomeVal where the date is the max date and to 0 otherwise
So my table would look like this
id SomeVal SomeDate 1 A 2011-01-01 0 A 2010-01-01 0 A 2009-01-01 1 B 2011-01-01 0 B 2010-01-01 1 C 2011-01-01 0 C 2010-01-01 0 C 2009-01-01
First let’s do the select statement, in order to accomplish what we set out to do we can use the row_number function, partition by SomeVal and order by SomeDate descending
Run the following statement
;WITH cte AS(SELECT
ROW_NUMBER() OVER(PARTITION BY SomeVal
ORDER BY SomeDate DESC) AS row,
* FROM SomeTable)
SELECT * FROM cte
Here is our result
row id SomeVal SomeDate 1 NULL A 2011-01-01 2 NULL A 2010-01-01 3 NULL A 2009-01-01 1 NULL B 2011-01-01 2 NULL B 2010-01-01 1 NULL C 2011-01-01 2 NULL C 2010-01-01 3 NULL C 2009-01-01
So where row is 1 we will make SomeVal 1 otherwise we will make it 0, we can do that with a simple case statement
Here is what the update looks like
;WITH cte AS(SELECT
ROW_NUMBER() OVER(PARTITION BY SomeVal
ORDER BY SomeDate DESC) AS row,
* FROM SomeTable)
UPDATE cte
SET id = CASE WHEN row = 1 THEN 1 ELSE 0 END
Now run the select against the table again
SELECT * FROM SomeTable
id SomeVal SomeDate 1 A 2011-01-01 0 A 2010-01-01 0 A 2009-01-01 1 B 2011-01-01 0 B 2010-01-01 1 C 2011-01-01 0 C 2010-01-01 0 C 2009-01-01
And as you can see the id column was updated correctly.
As you can see, using a common table expression can simplify your update statements.
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
