Solved

Update query - Sql Server, tsql

Posted on 2014-01-01
2
544 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
Comment Utility
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
Comment Utility
Thanks Chaau.. your query works perfectly for me..

Thanks a lot :)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now