SQL Update row by row

Question

My SQL table has 2 fields that impact my problem: "Priority" as real, and Start_Date as datetime. As the user adds items to the table, (s)he specifies the Priority for a given Start_Date.

If Item1 is Priority 1, then Item2 may be Priority 2 and it will be given a Start_Date after Item 1.

But if Item2 is given Priority 0.5 (or any number less than the Priority of Item1), then its Start_Date will be before Item 1's Start_Date.

After each Item is added, I want to go through the list of Items and Update all of the Priorities so they are integers, starting with the earliest Start_Date with a Priority =1, then 2, 3, etc.

I am working with SQL 2005. I know I can do this with a cursor. Is there a better way? How so?

Thank you.

Answer 1

You could do this with a temporary table. There might be better ways, but I can't think of one right now. This also assumes that you have a primary key on your table - if it's only start date and priority then this solution wouldn't be feasible.

create table #tempOrder (newPriority int identity,
                         id int)

insert into #tempOrder (id) select id from table order by Start_Date

update table set priority = newPriority 
  from #tempOrder
  where #tempOrder.id = table.id

One thing I don't understand with your problem is the mention of a priority of 0.5. You mention that priorities are assigned in order of start date, but also mention manually setting a priority to 0.5. When would this occur? What should be the behaviour if the start date of that priority conflicts with the ordering of priorities by start date?

Answer 2

UPDATE mt
SET Priority = 
    (
     SELECT COUNT(*) 
     FROM MyTable mt2 
     WHERE mt2.Start_Date <= mt.Start_Date
    )
FROM MyTable mt

Answer 3

If all the startdatetimes are unique, Try this (inside of a Begin Trans so you can review the result and rollback if it's not what you want)

   Update Table Set 
     Priority = (Select Count(*) From table 
                 Where Start_Date <= T.Start_Date)
   From Table T
   Where Start_Date > getDate()

if they are not unique, you will get some rows with the same Priority, (where the DateTimes are the same) and some gaps in the Priority Sequences...