Problem with MySql INSERT MAX()+1
increment, insert, max, mysql, sql
Solution
You can use something like this:
INSERT INTO users (user_id, name)
SELECT 1 + coalesce((SELECT max(user_id) FROM users WHERE name='Bob'), 0), 'Bob';
But such query can lead to a race condition. Make sure you are in a transaction and you lock the users table before running it. Otherwise you might end up with two Bobs with the same number.
Problem
I have a single table containing many users. In that table I have column called user_id (INT), which I want increment separately for each person. user_id MUST start at 1 I've prepared a simple example: ``` Showing all names +--------------+-----------------------+ | user_id | name | +--------------+-----------------------+ | 1 | Bob | | 1 | Marry | | 2 | Bob | | 1 | John | | 3 | Bob | | 2 | Marry | +--------------+-----------------------+ Showing only where name = Bob +--------------+-----------------------+ | user_id | name | +--------------+-----------------------+ | 1 | Bob | | 2 | Bob | | 3 | Bob | +--------------+-----------------------+ ``` The following query will do this, but it will only work if 'Bob' already exists in the table... ``` INSERT INTO users(user_id, name) SELECT(SELECT MAX(user_id)+1 from users where name='Bob'), 'Bob'; ``` If Bob does not exist (first entry) user_id is set to 0 (zero). This is the problem. I need the user_id to start from 1 not 0.