CTE and quirky update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CTE and quirky update

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-15 : 00:40:13

Hi, consider the following:

DECLARE @TBL TABLE (ID INT IDENTITY(1,1), NAME VARCHAR(100), AGE INT, DATESTART DATETIME, DATEEND DATETIME, Num INT DEFAULT 0)

INSERT INTO @TBL (NAME, AGE,DATESTART,DATEEND)
SELECT 'A',10, '2013-01-01','2013-01-10'
UNION
SELECT 'A',10,'2013-01-01','2013-01-15'
UNION
SELECT 'A',10,'2013-01-01','2013-01-25'
UNION
SELECT 'A',10,'2013-01-01','2013-01-08'

--SELECT * FROM @TBL

DECLARE @NAME VARCHAR(100), @Num INT

;WITH CTE
AS
(
SELECT *, ROW_NUMBER()OVER(PARTITION BY NAME,AGE ORDER BY DATESTART, DATEEND DESC) AS ROWNUM
FROM @TBL
)

--SELECT * FROM CTE
UPDATE CTE
SET Num=@Num, @Num = CASE WHEN NAME = @NAME THEN 1 ELSE NULL END,@NAME= NAME

SELECT * FROM @TBL

The quirky update is not being applied on the order returned by cte but on the order of the rows in the table. What I wanted was to order by DateStart and DateEnd Desc and then apply the quirky update to update column Num to 1, so it should apply 1 to all rows but not to the one with DateEnd=2013-01-25 as this would be the very first row as per the cte, it should be 0. IF I omit the cte and insert the rows in the table in the order that I want then quirky update works but not with the CTE order.
What am I missing here?

Thanks

--------------------
Rock n Roll with SQL

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-15 : 02:03:56

Hi,
What are you trying to do with the above query...
What should be tha toutput for above sample.... and explain output...

--
Chandu

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-15 : 02:27:07

Hi,

This is the current output of (after using quirky UPDATE)-> SELECT * FROM @TBL ORDER BY DATESTART, DATEEND DESC

ID NAME AGE DATESTART DATEEND NUM
4 A 10 2013-01-01 00:00:00.000 2013-01-25 00:00:00.000 1
3 A 10 2013-01-01 00:00:00.000 2013-01-15 00:00:00.000 1
2 A 10 2013-01-01 00:00:00.000 2013-01-10 00:00:00.000 1
1 A 10 2013-01-01 00:00:00.000 2013-01-08 00:00:00.000 NULL

Expected OUTPUT (after using quirky UPDATE) of -> SELECT * FROM @TBL ORDER BY DATESTART, DATEEND DESC
(please note IF do GET this output if i use a table directly instead of CTE provided the table is ordered by DATESTART ASC, DATEEND DESC)

ID NAME AGE DATESTART DATEEND NUM
4 A 10 2013-01-01 00:00:00.000 2013-01-25 00:00:00.000 NULL
3 A 10 2013-01-01 00:00:00.000 2013-01-15 00:00:00.000 1
2 A 10 2013-01-01 00:00:00.000 2013-01-10 00:00:00.000 1
1 A 10 2013-01-01 00:00:00.000 2013-01-08 00:00:00.000 1

If you see both the outputs, it needs to be ordered by datestart asc,dateend desc. that is the key but with CTE it's not working.

--------------------
Rock n Roll with SQL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-15 : 12:11:44

I could be wrong, but the Quirky Update relies on the Clustered Index on the table, so it would update in that fashion. Also, you should be applying the TABLOCX hint, etc.. If you check the article by Jeff Moden about it, he says trying to update in CTE order doesn't work (or that is my interpretation):
http://www.sqlservercentral.com/articles/T-SQL/68467/

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-16 : 07:57:11

Thanks Lamprey and your understanding is very correct. Thanks for sharing the article, quirky update seems to work correctly only with a clustered index.
Here is a modified version, this works but not without the primary key:

CREATE TABLE #TBL(ID INT IDENTITY(1,1) PRIMARY KEY, NAME VARCHAR(100), AGE INT, DATESTART DATETIME, DATEEND DATETIME, Num INT DEFAULT 0)

SET IDENTITY_INSERT #TBL ON

INSERT INTO #TBL(ID,NAME, AGE,DATESTART,DATEEND)
VALUES (3,'A',10, '2013-01-01','2013-01-10'),
(2,'A',10,'2013-01-01','2013-01-15'),
(1,'A',10,'2013-01-01','2013-01-25'),
(4,'A',10,'2013-01-01','2013-01-08')

--SELECT * FROM @TBL

DECLARE @NAME VARCHAR(100), @Num INT

;WITH CTE
AS
(
SELECT *, ROW_NUMBER()OVER(PARTITION BY NAME,AGE ORDER BY DATESTART, DATEEND DESC) AS ROWNUM
FROM #TBL
)

--SELECT * FROM CTE
UPDATE CTE
SET Num=@Num, @Num = CASE WHEN NAME = @NAME THEN 1 ELSE NULL END,@NAME= NAME

SELECT * FROM #TBL

DROP TABLE #TBL

Edit: don't really need the CTE above, it does not work without a clustered index on the table.

Thanks

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources