XML FeedsSomeone 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.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.