SQL Update row by row

My SQL table has 2 fields that impact my problem: "Priority" as real, 开发者_StackOverflow中文版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.

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...

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?

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