set based update not exactly right

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

SQL Server Development (2000)

set based update not exactly right

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-20 : 19:35:42

Hey all
i have a slight problem in this trigger
i have a table DELAY PK(DelayID)
this is what the table looks like
delayID OrderId
1..........1
2..........2
3..........3
4..........4
5..........5
(note: the delayid is not always in order i.e could be)
delayID OrderId
1..........1
2..........2
4..........3
6..........4
9..........5
(but the orderid should be in order at all times)
i have this trigger
CREATE TRIGGER KEEPORDERONDELETE ON DELAY
after DELETE
AS
SET NOCOUNT ON

UPDATE DELAY SET OrderId = d.OrderId - 1
FROM DELAY d, deleted del
WHERE d.OrderId > del.OrderId
go
(it should -1 of every orderid for each deleted row that follows i.e reorder the orderid's so there is no gap in flow of numbers)

and when i perform this query

delete from delay where delayid = 2 OR delayid = 4
i get this
delayID OrderId
1..........1
3..........2
5..........4

what i wanted was

delayID OrderId
1..........1
3..........2
5..........3

anyone help :) want to try to do this withouth using a cursor if possible

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 19:43:35

CREATE TRIGGER KEEPORDERONDELETE ON DELAY
after DELETE
AS
SET NOCOUNT ON

SELECT delayID, identity(int, 1, 1) as OrderId
into #temp
from delay

UPDATE DELAY
SET OrderId = t.OrderId
FROM DELAY d, #temp t
WHERE d.delayID = t.delayID

drop table #temp
go

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 19:51:14

I changed my mind. This is using the ranking solution. It's better since it doesn't use a temp table:



CREATE TRIGGER KEEPORDERONDELETE ON DELAY
after DELETE 
AS
SET NOCOUNT ON 

update d 
set orderid = t.orderid
from [delay] d
inner join 
(
	select 
		d1.delayid, 
		orderid = 
		(
			select count(*) + 1  
			from [delay] d2 
			where d2.delayid < d1.delayid
		)
	from [delay] d1
) t
on d.delayid = t.delayid
go

Tara

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-20 : 20:07:37

thats some nifty code

exactly what i was after cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources