Resequence column

Any ideas how to resequence the numbers in a column after inserting or
updating a record. The column is not the Unique ID and is an integer column
it is used to allow users to select the order in which things are displayed
for them the column is displayOrder and the select statement would Order By
that column. I am trying to do this in a stored procedue but I am not
having any luckPlease read: www.aspfaq.com/5006
Can you provide some more information including DDLs, sample data & expected
results, so that others can better understand your requirements?
Anith|||Assuming it's just a sequential resequencing you want and there
are no foreign key issues:
create table test (id int, name varchar(50))
insert into test select 5, 'test 1'
insert into test select 6, 'test 2'
insert into test select 9, 'test 3'
select * from test
declare @.id int
set @.id = 0
update test
set @.id = id = @.id + 1
"Ian Galloway via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:9aea371c6f2e4868b71c18edec3f9a87@.SQ
webservertalk.com...
> Any ideas how to resequence the numbers in a column after inserting or
> updating a record. The column is not the Unique ID and is an integer
column
> it is used to allow users to select the order in which things are
displayed
> for them the column is displayOrder and the select statement would Order
By
> that column. I am trying to do this in a stored procedue but I am not
> having any luck|||Here is an example of what I am trying to do
tbl_Browsers
ID browserName browserVersion displayOrder
1 Intern Explorer 6.5 1
2 Intern Explorer 6.0 2
3 Intern Explorer 5.0 3
4 Intern Explorer 4.0 4
5 Firefox 1.1 5
5 Firefox 1.2 6
the displayOrder columns holds the users preference of which order he would
like to display these records, as he inserts new records he may want the
newer record to be diplayed third in the list so I created this procedure:
CREATE PROCEDURE sp_sptInsertTicketStatusType
(@.strBrowserName VarChar(20), @.strBrowserVersion VarChar(225),
@.intDisplayOrder Int)
AS
update tbl_Browser set displayOrder = (displayOrder + 1)
where displayOrder >= @.intDisplayOrder
insert tbl_Browsers (browserName, browserVersion, displayOrder) values
(@.strName, @.strDescription, @.intDisplayOrder)
GO
Thhis would increment all the displayOrder Values by one where the
submitted display order is equal to or greater than the newly submitted
records displayOrder.
The problem is when editing the records to update the displayOrder things
can get out of sequence that way. I need a way to do the following
Edit a record - say ID 1 (IE 6.5) and make its displayOrder 3 - I need a
way to decrement the displayOrder of the records with displayOrder of 1
and 2 (or lesser values) by one and increment the others to fit
displayOrder 3 into the sequence so ultimately the table would look like
this - of course this would be dynamic as the display order can be any
value
ID browserName browserVersion displayOrder
2 Intern Explorer 6.0 1
3 Intern Explorer 5.0 2
1 Intern Explorer 6.5 3
4 Intern Explorer 4.0 4
5 Firefox 1.1 5
5 Firefox 1.2 6|||Not sure how you are accomodating multi-row inserts & updates with your
existing logic. Ignoring that, based on your narrative, you could do:
CREATE TABLE Browsers(
Browser VARCHAR( 20 ) NOT NULL,
Version DECIMAL( 2, 1 ) NOT NULL,
DisplayOrder INT NOT NULL
PRIMARY KEY ( Browser, Version ) ) ;
-- for inserts
BEGIN TRAN
UPDATE Browsers SET displayOrder = displayOrder + 1
WHERE displayOrder >= @.DisplayOrder;
INSERT Browsers SELECT @.Browser, @.version, @.DisplayOrder ;
COMMIT
-- for updates
BEGIN TRAN
UPDATE Browsers
SET displayOrder = displayOrder - 1
WHERE displayOrder <= @.DisplayOrder
...
UPDATE Browsers
SET displayOrder = @.DisplayOrder
WHERE Browser = 'Intern Explorer'
AND Version = 6.5
COMMIT TRAN
Anith|||I hope this can clarify a bit more what my desired results are, using the
table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
able to select the display order of the record. For inserting new records I
use the following.
This is the isert procedure
________________________________________
_____________
-- If user selects default dispayOrder (0) then add it as the last item to
display
IF @.intDisplayOrder = 0
BEGIN
Set @.intDisplayOrder = (select count(displayOrder) from
tbl_sptTicketStatus)+1
BEGIN TRAN
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
ELSE
-- If a diplayOrder is entered into the user form then insert it into the -
- proper place and increment all records equal to or greater than it
IF @.intDisplayOrder >= 1
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
where displayOrder >= @.intDisplayOrder
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
__________________________
For the EDIT procedure I need to make sure that if the user changes the
diplayOrder that the other records resequence accoringly, here is what I
have now for the procedure, but it does not get the desired affect
__________________________
IF @.intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
WHERE displayOrder <= @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus
SET displayOrder = (displayOrder + 1)
WHERE displayOrder > @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
GO
_______________________
so using just the ID and displayOrder column here is what should happen
original data
ID diplayOrder
10 1
20 2
30 3
40 4
now the user may want to make ID 40 the first option in a list which should
make the table look like this
ID diplayOrder
40 1
10 2
20 3
30 4
Then the user may choose to make the displayOrder of ID 10 to become third
which would make the table look like this
ID diplayOrder
40 1
20 2
10 3
30 4
so when I pull the data I will use Order By displyOrder.
This and other tables using this method may have any number or rows.
Thanks for any assistance.
Message posted via http://www.webservertalk.com|||I hope this can clarify a bit more what my desired results are, using the
table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
able to select the display order of the record. For inserting new records I
use the following.
This is the isert procedure
________________________________________
_____________
-- If user selects default dispayOrder (0) then add it as the last item to
display
IF @.intDisplayOrder = 0
BEGIN
Set @.intDisplayOrder = (select count(displayOrder) from
tbl_sptTicketStatus)+1
BEGIN TRAN
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
ELSE
-- If a diplayOrder is entered into the user form then insert it into the -
- proper place and increment all records equal to or greater than it
IF @.intDisplayOrder >= 1
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
where displayOrder >= @.intDisplayOrder
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
__________________________
For the EDIT procedure I need to make sure that if the user changes the
diplayOrder that the other records resequence accoringly, here is what I
have now for the procedure, but it does not get the desired affect
__________________________
IF @.intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
WHERE displayOrder <= @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus
SET displayOrder = (displayOrder + 1)
WHERE displayOrder > @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
GO
_______________________
so using just the ID and displayOrder column here is what should happen
original data
ID diplayOrder
10 1
20 2
30 3
40 4
now the user may want to make ID 40 the first option in a list which should
make the table look like this
ID diplayOrder
40 1
10 2
20 3
30 4
Then the user may choose to make the displayOrder of ID 10 to become third
which would make the table look like this
ID diplayOrder
40 1
20 2
10 3
30 4
so when I pull the data I will use Order By displyOrder.
This and other tables using this method may have any number or rows.
Thanks for any assistance.
Message posted via http://www.webservertalk.com|||Here's a revision of the procedure sans any transactions. Add them where
you like.
Try it out and let me know how it works out.
create PROCEDURE sp_sptInsertTicketStatusType
(@.strBrowserName VarChar(20), @.strBrowserVersion VarChar(225),
@.intDisplayOrder Int)
AS
declare @.olddisplayorder tinyint
select @.olddisplayorder = displayorder
from tbl_browser
where browserName = @.strBrowserName
and browserversion = @.strBrowserVersion
delete
from tbl_browser
where browserName = @.strBrowserName
and browserversion = @.strBrowserVersion
if @.intDisplayOrder > @.olddisplayorder
begin
update tbl_Browser
set displayOrder = (displayOrder - 1)
where displayorder between @.olddisplayorder and @.intDisplayOrder
end
if @.intDisplayOrder < @.olddisplayorder
begin
update tbl_Browser
set displayOrder = (displayOrder + 1)
where displayorder between @.intDisplayOrder and @.olddisplayorder
end
if (@.intDisplayOrder = 0)
select @.intDisplayOrder = max(displayorder) + 1
from tbl_Browser
insert tbl_Browser (browserName, browserVersion, displayOrder) values
(@.strBrowserName, @.strBrowserVersion, @.intDisplayOrder)
GO
"Ian Galloway via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:aaf79ff55d9d4406bc34ebdab258dc6f@.SQ
webservertalk.com...
> I hope this can clarify a bit more what my desired results are, using the
> table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
> able to select the display order of the record. For inserting new records
I
> use the following.
> This is the isert procedure
> ________________________________________
_____________
> -- If user selects default dispayOrder (0) then add it as the last item to
> display
> IF @.intDisplayOrder = 0
> BEGIN
> Set @.intDisplayOrder = (select count(displayOrder) from
> tbl_sptTicketStatus)+1
> BEGIN TRAN
> INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
> values
> (@.strName, @.strDescription, @.intDisplayOrder)
> COMMIT TRAN
> END
> ELSE
> -- If a diplayOrder is entered into the user form then insert it into
he -
> - proper place and increment all records equal to or greater than it
> IF @.intDisplayOrder >= 1
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
> where displayOrder >= @.intDisplayOrder
> INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
> values
> (@.strName, @.strDescription, @.intDisplayOrder)
> COMMIT TRAN
> END
> __________________________
> For the EDIT procedure I need to make sure that if the user changes the
> diplayOrder that the other records resequence accoringly, here is what I
> have now for the procedure, but it does not get the desired affect
> __________________________
> IF @.intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
> [ID] = @.intID)
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus SET
> displayOrder = @.intDisplayOrder,
> [Name] = @.strName,
> [description] = @.strDescription
> WHERE [ID] = @.intID
> COMMIT TRAN
> END
> ELSE
> IF @.intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
> [ID] = @.intID)
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
> WHERE displayOrder <= @.intDisplayOrder
> UPDATE tbl_sptTicketStatus SET
> displayOrder = @.intDisplayOrder,
> [Name] = @.strName,
> [description] = @.strDescription
> WHERE [ID] = @.intID
> COMMIT TRAN
> END
> ELSE
> IF @.intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
> [ID] = @.intID)
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus
> SET displayOrder = (displayOrder + 1)
> WHERE displayOrder > @.intDisplayOrder
>
> UPDATE tbl_sptTicketStatus SET
> displayOrder = @.intDisplayOrder,
> [Name] = @.strName,
> [description] = @.strDescription
> WHERE [ID] = @.intID
> COMMIT TRAN
> END
> GO
> _______________________
> so using just the ID and displayOrder column here is what should happen
> original data
> ID diplayOrder
> 10 1
> 20 2
> 30 3
> 40 4
> now the user may want to make ID 40 the first option in a list which
should
> make the table look like this
> ID diplayOrder
> 40 1
> 10 2
> 20 3
> 30 4
> Then the user may choose to make the displayOrder of ID 10 to become third
> which would make the table look like this
> ID diplayOrder
> 40 1
> 20 2
> 10 3
> 30 4
> so when I pull the data I will use Order By displyOrder.
> This and other tables using this method may have any number or rows.
> Thanks for any assistance.
> --
> Message posted via http://www.webservertalk.com|||Armando Prato, that worked great and as desired. Thank you for your
assistance.
Message posted via http://www.webservertalk.com|||After testing I realized that deleting and recreating the record was
changing the ID field of the record which is the Identity seed, where I am
using this as a foriegn key in another table this would have broken
relationships. So I modified the proc a bit to insert the existing ID of
the record as opposed to creating a new ID. In order to acheive this the
SET IDENTITY_INSERT flag had to be set to on.
I also wanted to add error checking so that if an error occurs I do not
have a half complete transaction deleting a record and not updating it
propoerly. Here is the finished code I hope now that it is set up properly,
if any one sees errors or drawbacks feel free to let me know.
CREATE PROCEDURE sptBrowserTypeEdit
(@.intID Int, @.strBrowserName VarChar(20), @.strBrowserVersion VarChar(225),
@.intDisplayOrder Int)
AS
SET IDENTITY_INSERT tbl_sptBrowsers ON
declare @.olddisplayorder tinyint
select @.olddisplayorder = displayorder
from tbl_sptBrowsers
where [ID] = @.intID
BEGIN TRAN
delete from tbl_sptBrowsers where [ID] = @.intID
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
if @.intDisplayOrder > @.olddisplayorder
BEGIN
BEGIN TRAN
UPDATE tbl_sptBrowsers SET displayOrder = (displayOrder - 1) WHERE
displayOrder between @.olddisplayorder and @.intDisplayOrder
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
END
if @.intDisplayOrder < @.olddisplayorder
BEGIN
BEGIN TRAN
UPDATE tbl_sptBrowsers SET displayOrder = (displayOrder + 1) WHERE
displayOrder between @.intDisplayOrder and @.olddisplayorder
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
END
IF (@.intDisplayOrder = 0)
SELECT @.intDisplayOrder = MAX(displayOrder) + 1 FROM tbl_sptBrowsers
BEGIN TRAN
INSERT tbl_Browser ([ID], browserName, browserVersion, displayOrder)
VALUES (@.intID, @.strBrowserName, @.strBrowserVersion, @.intDisplayOrder)
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
GO
Message posted via http://www.webservertalk.comsql