How to avoid using a cursor - 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

SQL Server 2000 Forums

Transact-SQL (2000)

How to avoid using a cursor

Author

Topic

LLatinsky
Starting Member

38 Posts

Posted - 2003-01-29 : 19:33:28

This is a one-time maintenance job and I don't mind using a cursor in this situation but I was wondering whether it's possible to do this in one update using some kind of a join.

SQL SERVER 2000 Enterprise Edition
The table is around 10000 records
the columns of interest:

Column1 Column2 StartDatetime EndDatetime
1 1 1-29-2003 15:15:00.00 null
1 1 1-25-2003 15:15:00.00 null
1 1 1-24-2003 15:15:00.00 null

I need to update all EndDatetimes for records that have the same value in column1 to the value of the next StartDatetime minus 1 minute leaving the latest value of the EndDatetime blank
so it looks like this:
Column1 Column2 StartDatetime EndDatetime
1 1 1-29-2003 15:18 null
1 1 1-25-2003 15:15 1-29-2003 15:17
1 1 1-24-2003 15:15 1-25-2003 15:14

Thank you.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-29 : 20:30:42

Need better explanation.
This is not clear

quote:

I need to update all EndDatetimes for records that have the same value in column1 to the value of the next StartDatetime minus 1 minute leaving the latest value of the EndDatetime blank

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-29 : 21:00:50

update tbl
set EndDatetime = (select min(t2.StartDatetime) - '00:01:00' from tbl t2 where tbl.Column1 = t2.Column1 and t2.StartDatetime > tbl.StartDatetime)

you can also put a

where exists (select * from tbl t2 where tbl.Column1 = t2.Column1 and t2.StartDatetime > t2.StartDatetime)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-29 : 21:07:57

Nr,

That gives

Column1 Column2 StartDatetime EndDatetime
1 1 1-29-2003 15:15 null
1 1 1-25-2003 15:15 1-29-2003 15:14
1 1 1-24-2003 15:15 1-25-2003 15:14

LLatinsky is this what you want because you're example doesn't match your question??

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-29 : 21:53:35

Think it should be

Column1 Column2 StartDatetime EndDatetime
1 1 1-29-2003 15:18:00.00 null
1 1 1-25-2003 15:15:00.00 null
1 1 1-24-2003 15:15:00.00 null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

LLatinsky
Starting Member

38 Posts

Posted - 2003-01-30 : 08:32:29

Thank you, very much, nr. This is exactly what I needed. ValterBorges, sorry for the typo (was supposed to be 18). I was trying to fit a row in one line - had to get rid of seconds and milliseconds part and accidentally replaced an 8 with a 5.

create table tbl (column1 int, column2 int, startdatetime datetime, enddatetime datetime)

insert tbl select
1, 1, '1-29-2003 15:18:00.00', null
union select
1, 1, '1-25-2003 15:15:00.00', null
union select
1, 1, '1-24-2003 15:15:00.00', null
select*from tbl
update tbl
set EndDatetime = (select min(t2.StartDatetime) - '00:01:00' from tbl t2 where tbl.Column1 = t2.Column1 and t2.StartDatetime > tbl.StartDatetime)
select*from tbl
drop table tbl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources