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

sql 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

sql
CREATE TABLE testNow(id int)
INSERT testNow VALUES(1)

Now run the following which will run an update statement against the CTE

sql ;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

sql
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.

sql ;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

sql
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

sql 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

sql
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

sql ;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

<pre>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</pre>

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

sql
;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

sql 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.