Value incrementation using update

Question

0.00/5 (No votes)

See more:

UPDATE MembersTest 
   Set StudentID = CONCAT('S-000',[key])
   WHERE [key] = [key]

What I have tried:

Any solutions on how to increment a value using update without using the above code

Posted 18-Feb-19 20:08pm

CaptainChizni

Updated 18-Feb-19 23:07pm

Add a Solution

2 solutions

Solution 2

Although Griff is right, another option would be to use a SEQUENCE, see explanation here: CREATE SEQUENCE (Transact-SQL) - SQL Server | Microsoft Docs[^]

You can CAST the sequence number to varchar like this:

CAST(NEXT VALUE FOR MySequence AS NVARCHAR(10))

Posted 18-Feb-19 23:07pm

RickZeeland

Updated 18-Feb-19 23:13pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2019-02-18T20:32:00

That code doesn't increment anything: it sets a column value to a fixed string and an existing column value.
And there is a better way to do that anyway: Specify Computed Columns in a Table - SQL Server | Microsoft Docs[^] Use that, and the column data doesn't need to be stored or updated at all - saving space - and can be retrieved at any time using a normal SELECT.

But incrementing values via an UPDATE in a DB are a bad idea: they lead to intermittent an difficult to find problems in production because at some point two or more users will try to update to the same value for different rows - and your data will become compromised as a result. Instead of manually incrementing values, use an IDENTITY column, and let SQL sort it out for you - you don't need to know a student ID before the data is stored in the DB anyway - honest! IDENTITY (Property) (Transact-SQL) - SQL Server | Microsoft Docs[^]