How to update 3 records using only one update?

I want to change seqid :

• 2 instead of 1
• 3 instead of 2
• 4 instead of 3

using one update statement.

UserID  seqid
1        1 
1        2
1        3  开发者_如何学运维
1        4

Make sure you make a backup before running any update in case you get it wrong.

Assuming (as per your question) you do not want seqid 4 modified:

update MyTable
set seqid = seqid + 1
where seqid between 1 and 3

If you just want to increment every seqid, do:

update MyTable
set seqid = seqid + 1

Edited to add: If you want to see the results of what you are doing before you commit to it (a good idea when making this kind of change):

begin tran
select seqid, count(*) 
from Mytable 
where seqid between 1 and 4

update MyTable 
set seqid = seqid + 1 
where seqid between 1 and 3 

select seqid, count(*) 
from Mytable 
where seqid between 1 and 4
--rollback tran
--commit tran

Don't forget to commit if you see the number of Seqids is right and don;t leave the transaction open especially on prod! But this is areally good idea for testing an update or delete when you aren't positive what the affect would be.

Another technique to try if you have a complicated update is:

update mt
set seqid = seqid + 1 
--select seqid, seqid+1, * 
From MyTable mt
where seqid between 1 and 3 

This way you can run the select and see in advance exactly what every value will be changed to.

This is the more general case, which makes no assumptions about how the old and new values are related:

update foo
set seqid = case seqid
              when 1 then 2
              when 2 then 3
              when 3 then 4
            end
where seqid in (2,3,4)