Updating sequential records after a delete query question!

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)

Updating sequential records after a delete query question!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-31 : 09:07:01

Heath writes "Hello, I am having some problems with a SQL query for SQL server 2000/7. I can accomplish what I want to do in straight ASP, but I figured there had to be a better way to do it in the database alone.

I am running both SQL Server 2000 and 7.0 on Win2000 clients and NT and Win2000 Servers. Not sure what Service packs we have at the office.

Here is the scenario. To make the example simple let's say I have a table with 10 records. These records contain a key field that links back to their parent record that is in another table. There is also another key field that contains the sequence value for the records (the two together establish the primary key of course). I need to delete the record that has the sequence value of '4'. Once that is done I now have a gap in my sequencing (1,2,3,5,6,7,8,9,10). Is there a way in SQL to say something of the following?:

UPDATE thetable SET (my sequence value to one less than my current value) WHERE (the sequence value is between 5 AND 10)

This would then shore up the gap and give us 1,2,3,4,5,6,7,8,9

Thanks for your help!"

dsdeming

479 Posts

Posted - 2002-05-31 : 10:21:39

Try

UPDATE thetable SET mysequencevalue = mysequencevalue - 1
WHERE mysequencevalue between 5 AND 10

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 10:38:25

Typically, these types of sequence numbers are used for ordering a resultset later.

If that is the case, the gap won't make a difference.

If that's not the case, it may be easier leave the gap to 'generate' a new sequence number at querytime based on the order by mysequencevalue.

<O>

MakeYourDaddyProud

184 Posts

Posted - 2002-05-31 : 11:49:15

That's assuming 10 is the maximum number. What if that and the 'gap' number is not known?

I wanted a subquery to isolate the rows having IDs 1 and 2 ahead, we can then drill down to the
exact rows with 1 ahead is a null and 2 and not null i.e. a GAP. Here goes...

drop table #thetable
go

create table #thetable
(id int, -- primary key
link_id int, -- foreign ket
sumdata varchar(123)
)
Go

insert into #thetable values (1, 20, "Mum")
insert into #thetable values (2, 20, "Dan")
insert into #thetable values (3, 20, "Sister")
insert into #thetable values (5, 20, "Brother")
insert into #thetable values (6, 20, "Son")
go

then we have t0:

1) left outer join twice 1 for the next row and 1 for the second
2) filter to the exact condition i.e. t2.id null and t3.id not null i.e. a gap
3) bind a subquery to delete on a scalar tuple value

So we have:

UPDATE #thetable
SET id = id - 1
WHERE id > (SELECT t1.id
FROM #thetable t1
LEFT JOIN #thetable t2
ON t2.id = t1.id + 1
LEFT JOIN #thetable t3
ON t3.id = t1.id + 2
WHERE t2.id IS NULL
AND t3.id IS NOT NULL)

select * from #thetable

id link_id sumdata
----------- ----------- -------
1 20 Mum
2 20 Dan
3 20 Sister
4 20 Brother
5 20 Son

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources