Update Values to MIN Value After Delete

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)

Update Values to MIN Value After Delete

Author

Topic

BlackDog
Starting Member

18 Posts

Posted - 2005-01-25 : 14:14:57

Not sure if that subject fits the question, but oh well.

Given a table:
CREATE TABLE tmpNumbers
(ParentId int NOT NULL,
ForeignId int NOT NULL,
Number int NOT NULL)
GO
ALTER TABLE tmpNumbers ADD CONSTRAINT pk PRIMARY KEY CLUSTERED
(ParentId, ForeignId)
GO
CREATE INDEX ixNumber ON tmpNumbers(Number)

The data looks like:
ParentId, ForeignId, Number
1, 90, 1
1, 91, 2
3, 88, 1
3, 78, 2
3, 77, 3
3, 99, 4
4, 29, 1

So that I have a sequential value in the Number column starting at 1 for each record within the same ParentId value.

If I delete a record, say the (3, 88, 1) record, I need to update the remaining records for ParentId 3 so the Number starts at 1 again.

What I have right now is the following, but it runs pretty slow. I'm looking for recommendations on a faster way to do this.


UPDATE	tn
SET tn.Number = tn.Number + 1 - (SELECT MIN(tn2.Number) FROM tmpNumbers tn2
WHERE tn2.ParentId = tn.ParentId)
FROM	tmpNumbers tn

Thanks.

Ryan

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-25 : 15:36:17

Are there any rules for how the numbers get assigned? Or can any order be imposed?

HTH

=================================================================

Our elections are free, it's in the results where eventually we pay. -Bill Stern, sports announcer (1907-1971)

BlackDog
Starting Member

18 Posts

Posted - 2005-01-25 : 17:00:21

There are rules when the data is first inserted, but in this case the only rule that needs to be inforced is making sure the sequential order remains the same within a given ParentId group. So if a given ParentId group starts out with Number values of 1,2,3,4,5 and then after a delete ends up looking like 3,4,5, I need to run an update statement that sets the 3 to 1, the 4 to 2 and the 5 to 3.

I hope that makes sense.

Thanks.

Ryan

BlackDog
Starting Member

18 Posts

Posted - 2005-01-26 : 14:48:02

BTT

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-26 : 19:01:02

The hard thing in a trigger is to cater for multiple deletes.

create table tmpNumbers(ParentId int, ForeignId int, Number int)
GO

create trigger trgdelanumber on tmpNumbers for delete as
set nocount on
update t set
	Number = t.Number - n.cnt
from
	tmpNumbers t
	join
( 	select
		t.ParentId,
		t.ForeignId,
		count(*) as cnt
	from
		tmpNumbers t join deleted d
			on t.ParentId = d.ParentId
			and t.Number > d.Number
	group by
		t.ParentId, t.ForeignId ) n
	on t.ParentId = n.ParentId and t.ForeignId = n.ForeignId
GO


insert tmpNumbers(ParentId,ForeignId,Number)
select 1, 90, 1
union all select 1, 91, 2
union all select 3, 88, 1
union all select 3, 78, 2
union all select 3, 77, 3
union all select 3, 99, 4
union all select 3, 188, 5
union all select 3, 178, 6
union all select 3, 177, 7
union all select 3, 199, 8
union all select 4, 29, 1


select * from tmpNumbers
delete tmpNumbers where ParentId = 3 and number in(3,5,7)
select * from tmpNumbers

drop table tmpNumbers
GO

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-26 : 19:04:57

The order of the rows should be of no importance.
What is the business rule the Number column represents ?
And why does it have to be updated at all ?

rockmoose

BlackDog
Starting Member

18 Posts

Posted - 2005-01-28 : 10:32:34

Thanks for the trigger. I'm going to be doing the update through a procedure, but I should be able to take that logic and put it to good use.

You're correct, the order of the rows is of no importance. This table is used as a de-normalized view of another table and is utilized in a search. The values in the number column need to be maintained in sequential order within the ParentId because the search uses those values to determine the result set. The values need to be sequential (i.e., no breaks in a sequence within a given ParentId), but do not necessarily have to be reset to start at one. The reset back to one is just a bonus so down the road someone doesn't have to worry about finding the min value within a ParentId, they can just find where number = 1.

Thanks.

Ryan

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-28 : 10:39:45

You are welcome.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources