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

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 2

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 3

update tab_name

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

Answer 4

you do no longer likely choose code nor construct a application to accomplish this. you ought to use the information Transformation centers (DTS) that comprise sq. Server 2000 to append your records. yet another greater handy decision is importing the information applying the Import / Export wizard in company supervisor. wish this facilitates.