Solved

Update query - Sql Server, tsql

Posted on 2014-01-01
2
586 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

624 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