Need help to update table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help to update table

Author

Topic

ANDRE726
Starting Member

1 Post

Posted - 2010-01-26 : 22:01:14

I have 4 columns of interest in my table
userid...int
groupcode...char(10)
startdate...datetime
enddate....datetime

records are organized by userid by groupcode
example

125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:15 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:17 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:19 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:22 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:25 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:29 AM

Each group has the same startdate throughout with different enddate. What I am trying to do is to change the next startdate/time to the previous record enddate/time. Results would look as follows

125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:15 AM
125 AH0055A 1/22/2010 08:15 AM 1/22/2010 08:17 AM
125 AH0055A 1/22/2010 08:17 AM 1/22/2010 08:19 AM
125 AH0055A 1/22/2010 08:19 AM 1/22/2010 08:22 AM
125 AH0055A 1/22/2010 08:22 AM 1/22/2010 08:25 AM
125 AH0055A 1/22/2010 08:25 AM 1/22/2010 08:29 AM

I can do this manually for 10 or 20 records but I have 25,000 records to update. Can anyone enlighten me as to how this can be done. Any help would be greatly appreciated.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-01-26 : 23:29:38

try this

SET NOCOUNT ON

DECLARE @T_TABLE TABLE
(
		userid int
		, groupcode char(10)
		, startdate datetime
		, enddate datetime
)
INSERT INTO @T_TABLE
SELECT 125, 'AH0055A', '1/22/2010 08:00 AM', '1/22/2010 08:15 AM' UNION ALL
SELECT 125, 'AH0055A', '1/22/2010 08:00 AM', '1/22/2010 08:17 AM' UNION ALL
SELECT 125, 'AH0055A', '1/22/2010 08:00 AM', '1/22/2010 08:19 AM' UNION ALL
SELECT 125, 'AH0055A', '1/22/2010 08:00 AM', '1/22/2010 08:22 AM' UNION ALL
SELECT 125, 'AH0055A', '1/22/2010 08:00 AM', '1/22/2010 08:25 AM' UNION ALL
SELECT 125, 'AH0055A', '1/22/2010 08:00 AM', '1/22/2010 08:29 AM'

UPDATE T
SET startdate = ISNULL((SELECT MAX(enddate) FROM @T_TABLE WHERE userid = T.userid
					AND startdate = T.startdate AND enddate < T.enddate), startdate)
FROM @T_TABLE T

SELECT * FROM @T_TABLE

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-27 : 00:11:55

[code]Declare @T table
(Userid int,GroupCode Varchar(50),StartDate Datetime,EndDate Datetime)

Insert @T
Select 125 ,'AH0055A','1/22/2010 08:00 AM','1/22/2010 08:15 AM' union all
Select 125 ,'AH0055A','1/22/2010 08:00 AM','1/22/2010 08:17 AM' union all
Select 125 ,'AH0055A','1/22/2010 08:00 AM','1/22/2010 08:19 AM'

SELECT Userid,GroupCode,StartDate,EndDate,
ROW_NUMBER() OVER (Partition BY Userid,GroupCode,StartDate ORDER BY EndDate) AS rownum
into #temp from @T

update T
Set T.StartDate = M.PDate
from #temp T
inner join
( Select C.Userid,C.GroupCode,C.StartDate,C.EndDate as CurDate,P.EndDate as PDate,C.Rownum
from #temp C
Left join #temp P on C.rownum = P.rownum+1
)M on M.rownum = T.rownum and M.PDate is not null

Select * from #temp[/code]

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 03:51:33

I think, for a sizeable number of rows (and I would consider 25,000 enough) that sodeep's solution will be more efficient. Best to pre-create the #TEMP table though (to avoid blocking on TEMPDB). The nested SELECT MAX WHERE Less-than-self will be high CPU demand.

Only works for SQL 2005 onwards, for SQL 2000 you could insert into a #TEMP table with an Identity column and an Order By Userid, GroupCode, StartDate to get the same solution.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-27 : 07:36:27

Here is for SQL 2000.

SELECT identity(int,1,1)as rownum,Userid,GroupCode,StartDate,EndDate
into #temp from @T
Order by EndDate

To Speed up Query You can create index on Rownum in TempTable.

Create Clustered Index IDX_RowNum on #temp(Rownum)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources