Update query - Sql Server, tsql

Hello Experts,

I need help regarding an update query.

I am trying to generate a row_number to a field in a table using an update query.

Please run the below script for sample data.

CREATE TABLE dbo.TEMPTBL
(
 EDU_ID varchar(256) NULL
,SEGSTRT_UTC datetime NULL
,SEGNUM int NULL
)

INSERT INTO dbo.TEMPTBL
SELECT '526ff418000000000a0d6c6823360002','2013-10-29 17:51:37.000', NULL

INSERT INTO dbo.TEMPTBL
SELECT '526ff418000000000a0d6c6823360002','2013-10-29 17:56:51.000', NULL

INSERT INTO dbo.TEMPTBL
SELECT '526ff418000000000a0d6c6823360002','2013-10-29 17:51:37.000',NULL

INSERT INTO dbo.TEMPTBL
SELECT '526ff418000000000a0d6c6823360002','2013-10-29 17:56:51.000', NULL

INSERT INTO dbo.TEMPTBL
SELECT '52864276000200000a0d6c6623360002','2013-11-15 15:50:26.000', NULL

INSERT INTO dbo.TEMPTBL
SELECT '52864276000200000a0d6c6623360002','2013-11-15 15:50:26.000', NULL

INSERT INTO dbo.TEMPTBL
SELECT '52864276000200000a0d6c6623360002','2013-11-15 15:53:11.000',NULL

INSERT INTO dbo.TEMPTBL
SELECT '52864276000200000a0d6c6623360002','2013-11-15 17:14:08.000', NULL

SELECT * FROM dbo.TEMPTBL ORDER BY SEGSTRT_UTC


UPDATE T
 SET T.SEGNUM = RES.SEG_NUM FROM TEMPTBL AS T 
INNER JOIN (SELECT EDU_ID,SEGSTRT_UTC,ROW_NUMBER() OVER (PARTITION BY EDU_ID ORDER BY SEGSTRT_UTC ASC) AS SEG_NUM FROM TEMPTBL) RES
ON T.EDU_ID = RES.EDU_ID
AND T.SEGSTRT_UTC = RES.SEGSTRT_UTC

SELECT * FROM dbo.TEMPTBL ORDER BY EDU_ID,SEGSTRT_UTC 

Open in new window


I have 3 columns in table TEMPTBL. SEGNUM will be null when the data is populated. I have to write an update query to populate the ROW_NUMBER in the SEGNUM field based on the EDU_ID and SEGSTRT_UTC field.

Please look at the SEGNUM field after running the UPDATE query from above.

My update query is giving me wrong results... Please help me correcting it.

Kindly please help!!

Thanks in advance!!!
ravichand-sqlAsked:
Who is Participating?
 
chaauCommented:
This is the result of your ROW_NUMBER():
SELECT EDU_ID,SEGSTRT_UTC,
ROW_NUMBER() OVER (PARTITION BY EDU_ID ORDER BY SEGSTRT_UTC ASC) AS SEG_NUM 
FROM TEMPTBL

|                           EDU_ID |                     SEGSTRT_UTC | SEG_NUM |
|----------------------------------|---------------------------------|---------|
| 526ff418000000000a0d6c6823360002 |  October, 29 2013 17:51:37+0000 |       1 |
| 526ff418000000000a0d6c6823360002 |  October, 29 2013 17:51:37+0000 |       2 |
| 526ff418000000000a0d6c6823360002 |  October, 29 2013 17:56:51+0000 |       3 |
| 526ff418000000000a0d6c6823360002 |  October, 29 2013 17:56:51+0000 |       4 |
| 52864276000200000a0d6c6623360002 | November, 15 2013 15:50:26+0000 |       1 |
| 52864276000200000a0d6c6623360002 | November, 15 2013 15:50:26+0000 |       2 |
| 52864276000200000a0d6c6623360002 | November, 15 2013 15:53:11+0000 |       3 |
| 52864276000200000a0d6c6623360002 | November, 15 2013 17:14:08+0000 |       4 |

Open in new window

As you can see, you have used PARTITION BY EDU_ID ORDER BY SEGSTRT_UTC ASC. Therefore the row number gets reset on new EDU_ID. If you want it to include SEGSTRT_UTC, modify the PARTITION BY clause, like this:
SELECT EDU_ID,SEGSTRT_UTC,
ROW_NUMBER() OVER (PARTITION BY EDU_ID, SEGSTRT_UTC ORDER BY SEGSTRT_UTC ASC) AS SEG_NUM 
FROM TEMPTBL

|                           EDU_ID |                     SEGSTRT_UTC | SEG_NUM |
|----------------------------------|---------------------------------|---------|
| 526ff418000000000a0d6c6823360002 |  October, 29 2013 17:51:37+0000 |       1 |
| 526ff418000000000a0d6c6823360002 |  October, 29 2013 17:51:37+0000 |       2 |
| 526ff418000000000a0d6c6823360002 |  October, 29 2013 17:56:51+0000 |       1 |
| 526ff418000000000a0d6c6823360002 |  October, 29 2013 17:56:51+0000 |       2 |
| 52864276000200000a0d6c6623360002 | November, 15 2013 15:50:26+0000 |       1 |
| 52864276000200000a0d6c6623360002 | November, 15 2013 15:50:26+0000 |       2 |
| 52864276000200000a0d6c6623360002 | November, 15 2013 15:53:11+0000 |       1 |
| 52864276000200000a0d6c6623360002 | November, 15 2013 17:14:08+0000 |       1 |

Open in new window

However, you will not be able to use the Update query as it is now, because when you join ON T.EDU_ID = RES.EDU_ID AND T.SEGSTRT_UTC = RES.SEGSTRT_UTC SQL Server will not have a clue which row to join with.
I recommend you create a temporary IDENTITY column in your table that will help with the update.
So, the resulting update query will look like this:
SELECT * FROM dbo.TEMPTBL ORDER BY SEGSTRT_UTC;

ALTER TABLE  dbo.TEMPTBL add ID INT IDENTITY(1,1) NOT NULL;

;with RES AS 
(SELECT ID, EDU_ID,SEGSTRT_UTC,
ROW_NUMBER() OVER (PARTITION BY EDU_ID ORDER BY SEGSTRT_UTC ASC) AS SEG_NUM  -- use this
-- ROW_NUMBER() OVER (PARTITION BY EDU_ID,SEGSTRT_UTC ORDER BY SEGSTRT_UTC ASC) AS SEG_NUM    -- or this 
FROM TEMPTBL)
UPDATE T
 SET T.SEGNUM = RES.SEG_NUM FROM TEMPTBL AS T 
INNER JOIN RES
ON T.EDU_ID = RES.EDU_ID
AND T.SEGSTRT_UTC = RES.SEGSTRT_UTC AND T.ID = RES.ID

ALTER TABLE  dbo.TEMPTBL DROP COLUMN ID;

SELECT * FROM dbo.TEMPTBL ORDER BY EDU_ID,SEGSTRT_UTC 

Open in new window

0
 
ravichand-sqlAuthor Commented:
Thanks Chaau.. your query works perfectly for me..

Thanks a lot :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.