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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
AleksAuthor Commented:
Some users will want the next id value. Some customize that value to whatever they like.
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


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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
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.


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

Open in new window

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:
SET contactnumberstart  = (select ISNULL(max(UserID)+1, 1) fromUsers)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Thx. It's never null.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.