March 16, 2015 at 2:51 pm
Hello all,
I have some strange behavior I am noticing when trying to use ROW_NUMBER() with a UPDATE to a temp table. Can some one explain what is happening here?
In the ddl you will notice that if you select * from the CTE directly the ROW_NUMBER's are as anticipated. However if I try to update the temp table then they get wonky.
I know I am missing something here fundamental. Is it because with the update that the rows are not matched correctly because there is no guarantee on the ORDER?
Any explanation is appreciated.
CREATE TABLE #TEST (CatName VARCHAR (20), SubCat VARCHAR(10), RowNumber INT)
INSERT INTO #TEST VALUES ('Apple', 'Green' , 0),('Apple', 'Red' , 0),('Apple', 'Blue' , 0),('Apple', 'Yellow' , 0)
,('Watermellon', 'Green' , 0),('Apple', 'Green/Blue' , 0),('Apple', 'Red' , 0),('Apple', 'Purple' , 0);
WITH NumCte AS (
SELECT t.CatName
,RN = ROW_NUMBER() OVER(PARTITION BY t.CatName ORDER BY t.SubCat)
FROM #TEST T)
----SELECT * FROM NumCte ----I get different row numbers here than after the update.
UPDATE T
SET RowNumber = nc.RN
FROM #TEST T
INNER JOIN NumCte nc ON nc.CatName =t.CatName;
SELECT *
FROM #TEST
DROP TABLE #TEST
***SQL born on date Spring 2013:-)
March 16, 2015 at 3:00 pm
Based on your data that update won't work as expected because the rows aren't unique.
Try this.
WITH NumCte AS (
SELECT t.CatName, t.RowNumber,
RN = ROW_NUMBER() OVER(PARTITION BY t.CatName ORDER BY t.SubCat)
FROM #TEST T)
UPDATE NumCte SET RowNumber = RN
March 16, 2015 at 3:06 pm
That was it. Thank you
***SQL born on date Spring 2013:-)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply