Solved

Update query - Sql Server, tsql

Posted on 2014-01-01
2
566 Views
Last Modified: 2014-01-01
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!!!
0
Comment
Question by:ravichand-sql
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39750441
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
 

Author Closing Comment

by:ravichand-sql
ID: 39750482
Thanks Chaau.. your query works perfectly for me..

Thanks a lot :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question