Help with UPDATE query in SQL Server

H everybody I need one help, I need to change some IDs of product :

SELECT *
FROM user_item
SET item_type='8063'
WHERE it开发者_开发知识库em_type ='8051

Ok... easy, but have a problem

Sometimes I will need to update and add + 1 item. And I really don't know what to do.

Example :

set item_type='962' And add 963
where item_type='8077'

anyone here can help me please?

Tks

---

UPDATE user_item 
SET item_type = 962 + 1 
WHERE item_type = '8077'

---

;with CTE as
(   select  item_type,
            case
                when item_type = '962' then CONVERT(varchar(50), convert(int, item_type) + 963)
                else CONVERT(varchar(50), convert(int, item_type) + 1)
            end as new_item_type
    from    user_item )
update  CTE 
set     item_type = new_item_type

Ideally you should look up the new item type in some other table or by some other rule rather than making a large, ugly and brittle case statement. This example works for the information you've given in the question. Experiment with the SQL statement in the CTE before the update.

PS: Is your data type really a varchar? If you're performing mathematical operations on the data then they should certainly be integers. You'll get better lookup and indexing performance on them and they'll take less space than storing them as varchars.

---

Or if you want to add another field with item_type = 963 (if I understood properly), you can have an additional related insert query to do that when you change the item_type value of the item in question.