Solved

Update query - Sql Server, tsql

Posted on 2014-01-01
2
562 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get Duration of last Status Update 4 35
sql query 8 50
sql server select date from a datetime column 10 29
Email Notifications for SQL 2005 9 28
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

821 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