How to replace Cursor in SQL Server?

How do to update rows without a cursor?

• 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!

• Answer:

  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] bWHERE tablename.event_type=b.event_typeAND b.START_TIME>tablename.START_TIME)