Query to update with latest ID + 1

I have a table called 'users', each row has a unique ID (autonumber) -- userid
I have another table which is called 'firm', and a field (int) called:  contactnumberstart

I need to update firm.contactnumberstart  and make it =  to the latest (or highest) userid + 1
This way I know which is the next ID.

I only need to update it once, the system will take care of updating that number whenever a new user id added.

It should be something like:

update firm
set contactnumberstart   =   MAX(users.userid) + 1 

Open in new window


Well, that is the idea.
LVL 1
AleksAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Aleks, you're inventing the wheel. SQL Server has an object called SEQUENCE that does what you need (provide the next available value for the sequence name).

Btw, your solution works but will fail if table Users is empty, as it will return NULL. You just need to add an ISNULL function to validate that:
UPDATE Firm
SET contactnumberstart  = (select ISNULL(max(UserID)+1, 1) fromUsers)

Open in new window

0
 
Brian CroweDatabase AdministratorCommented:
Why?  What is the value in storing the seed value for one table in another and adding the overhead to maintain it?
0
 
AleksAuthor Commented:
Some users will want the next id value. Some customize that value to whatever they like.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Brian CroweDatabase AdministratorCommented:
Instead of trying to create a process that is likely to run into concurrency issues I would recommend taking advantage of SQL functions that already exist.

If you want the current max value of an IDENTITY column use IDENT_CURRENT

DECLARE @ID      INT;
SELECT @ID = IDENT_CURRENT('myTable')
SELECT @ID

If you want to know what the original seed value for an IDENTITY column was use IDENT_SEED

If you want to change the seed value of a table use DBCC CHECK_IDENT

DBCC CHECKIDENT('myTable, RESEED, 1000) --The next value assigned will be 1001 assuming your interval is one

Hope this helps
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(No points) I'm with Brian.  Sounds like your original solution is adding logic that can potentially break and cause problems, where a function is available to guarantee the correct answer.
0
 
AleksAuthor Commented:
I don't have the time to explain the reason of why this needs to get done, therefore I only asked the way to do it.
Here is the solution in case someone in the future needs to get this done:

Won't assign points as recommended.

Solution:

Firm
set contactnumberstart  = (select max(UserID)+1 fromUsers)

Open in new window

0
 
AleksAuthor Commented:
Thx. It's never null.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.