I need help with a SQL query that updates a table when a priority is changed.?

Question

Say I have a table with 3 records with priorities 1,2,3.

I update the table to that priority 1 is now priority 3 , therefore priority 2 is now priority 1 and priority 3 is now priority 2. How can I perform this query. The number of records is dynamic and it would need to work ether way if it was reassigning priority 1 to 3 or 3 to 1 .. there could be between recoreds with priority 1 through up to 100. How would I do this???

Answer 1

Here is some sample code to create a table and fully test this type of thing ... There are lots of ways to clean it up- but this should give you the general concept.

CREATE TABLE [dbo].[TestPri](

[Name1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Priority] [int] NOT NULL

) ON [PRIMARY]

GO

-- Populate Table

SET NOCOUNT ON

INSERT INTO TestPri (Name1, Priority) VALUES ('John', 1)

INSERT INTO TestPri (Name1, Priority) VALUES ('Hank', 2)

INSERT INTO TestPri (Name1, Priority) VALUES ('Suzy', 3)

INSERT INTO TestPri (Name1, Priority) VALUES ('George', 4)

INSERT INTO TestPri (Name1, Priority) VALUES ('Jackie', 5)

GO

ALTER PROC AlterPri

(

@OldPri int,

@NewPri int

)

AS BEGIN

SET NOCOUNT ON

-- If Increasing the Priority of a record..

IF @OldPri > @NewPri BEGIN

--Modify the record we're changing..

UPDATE TestPri SET Priority = (@NewPri-1) WHERE Priority = @OldPri

-- add +1 to each record prior to it's old setting

UPDATE TestPri SET Priority = (Priority+1) WHERE Priority < @OldPri

-- If Decreasing the Priority of a record..

END ELSE IF @OldPri < @NewPri BEGIN

--Modify the record we're changing.. add an additional +1 because on the

-- next query it will subtract it

UPDATE TestPri SET Priority = (@NewPri+1) WHERE Priority = @OldPri

-- minus 1 to each record prior to it's old setting

UPDATE TestPri SET Priority = (Priority-1) WHERE Priority > @OldPri

END

SELECT * FROM TestPri ORDER BY Priority

END

GO

Answer 2

Depends on what info you have going in...I'm assuming you know just the old and new priorities (also assuming all priority values are mutually exclusive, which is why you're shuffling in the first place):

UPDATE some_table

SET priority = 0

WHERE priority = old_priority

if new_priority > old_priority

UPDATE some_table

SET priority = priority - 1

WHERE priority <= new_priority AND priority >= old_priority

else

UPDATE some_table

SET priority = priority + 1

WHERE priority >= new_priority AND priority <= old_priority

endif

UPDATE some_table

SET priority = new_priority

WHERE priority = 0

Answer 3

update tab_name

set priority= case priority when 100 then 1 else priority+1 end