Sql update using case

Question

I am trying to update a column in sql server table which is used to generate ID's: Table structue is something like this

Name varchar(50), LastID long

In last ID we keep Last ID we have generated for something say StudentID. So data will look like this

Name :Student, LastID : 50001

This last ID is incremeting by 1 to generate a unique Id for (primary key).

Now there is a situation that where we have to reset this LastID to 0 when it reacheachs to Max (say 100000). So I am using

Update Sequence 
set    LastID = Case when LastID + @range >= @max then 0 else LastID end 
where  Name ='StudentID'

Range is parameter we pass to our store procedure generaly it's 100 in my case. So before seting the LastID I check whether it's exceiding Max for student ID i.e. 1,00,000.

What bothers me in above update query I am again updating LastID to it's current value if it's not excedding the range. Looking for a better alternative. What I don't want to do is:

Using select and keeping LastID in a variable or something like this.

I just want to do in a single update if it's possible.

It's a sql server 2008 databse so I can't use inbuilt sequence feature of sql server 2012.

If more inforamtion is required please let me know.

There just isn't enough detail to understand this. But [here](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) is a great place to start. — Sean Lange, Sep 13 '18 at 14:21
It looks like you're missing `then 0` before the `else` in your Case. — DavidP, Sep 13 '18 at 14:27
Skip the case expression. Move the condition the the WHERE clause instead. — jarlh, Sep 13 '18 at 14:30
What @jarlh said. If you don't want to update when the condition isn't true, you can exclude those rows in the `WHERE`. `Update Sequence set LastID = 0 where Name ='StudentID' AND LastID + @range >= @max` — cf_en, Sep 13 '18 at 14:32

lukaszberwid · Accepted Answer · 2018-09-13T14:58:36.893

If you really want to go with your sql code what you could try is

Update Sequence 
set    LastID = Case when LastID + @range >= @max then 0 else LastID end 
FROM Sequence
where  Name ='StudentID'

More on how to use this syntax is described here How do I UPDATE from a SELECT in SQL Server?

Moreover I'd not suggest using different approach in your case since you dont want to update value when LastID doesn't meet the criteria so as cf_en suggested filter those values in where clause and then only update the rest of the values

Update Sequence 
set    LastID = 0
FROM Sequence
where  Name ='StudentID' and LastID + @range >= @max

This query will be much faster since it won't update all rows in db with same value

Sql update using case

1 Answers1