How do to update rows without a cursor?

January 24, 2007 1:02 PM

How do I update rows in a table in a SQL*Server 2005 database with data from another row in the same table?

I have an event log type table that contains data as follows:
EVENT_TYPE START_TIME END_TIME
type1 10
type3 4
type2 5
type1 11
type1 15
type2 7
type2 9
type3 15
type1 23

For whatever reason, it turns out that I need to create a column called END_TIME and populate that with the BEGIN_TIME of the next event with the same EVENT_TYPE.

So, I would need to end up with

EVENT_TYPE START_TIME END_TIME
type1 10 11
type3 4 15
type2 5 7
type1 11 15
type1 15 23
type2 7 9
type2 9 x
type3 15 x
type1 23 x

So, if you see the first "type1" event, the END_TIME is the START_TIME of the next "type1" event which is 11.

Now, I suppose I can use a cursor to do this, but this DB has 10s of millions of rows and it's likely to take days, if not weeks to do this using a cursor. I've been Googling around and I see that some kind of Set base approach performs much better than a Cursor based approach.

My SQL-fu fails me here. Can you help?

FYI, I'm using SQL*Server 2005. I'm pertty weak at T-SQL so,... TIA!

posted by apark to computers & internet (5 comments total)

A subquery should do the trick, but I'm not sure what the performance would be like. I tested this using your sample dataset, and it seemed to work fine:

UPDATE [tablename]

SET END_TIME=

(

SELECT MIN(START_TIME) FROM [tablename] b

WHERE event_type=b.event_type

AND b.START_TIME>tablename.START_TIME

)

posted by Doofus Magoo at 1:21 PM on January 24, 2007

Whoops, scratch that. It's updating them absolutely, not based on EVENT_TYPE. It threw me when I was looking at the results because all of the type1 come before all of the type2, which come before all of the type3. Sorry about the confusion.
posted by Doofus Magoo at 1:26 PM on January 24, 2007

This should do the trick. It's the same as my first post, but I explicitly reference the table in the outer part of the nested query:

UPDATE [tablename]

SET END_TIME=

(

SELECT MIN(START_TIME) FROM [tablename] b

WHERE tablename.event_type=b.event_type

AND b.START_TIME>tablename.START_TIME

)

posted by Doofus Magoo at 1:27 PM on January 24, 2007 [1 favorite]

Thanks Doofus Magoo. Looks plausible to me. I just kicked it off on the server. Let's see what happens!
posted by apark at 1:53 PM on January 24, 2007

Doofus Magoo, you're the man. It worked like a charm. Thank you!
posted by apark at 3:01 PM on January 24, 2007