12

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.

Chad
  • 2,365
  • 6
  • 26
  • 37

4 Answers4

17

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.

Lukáš Lalinský
  • 40,587
  • 6
  • 104
  • 126
  • 2
    Yes, the query works, but please don't ignore the second part. :) Or if you decide to ignore it, at least create an unique index on `(name, user_id)` so that the query that would otherwise produce a duplicate fails. – Lukáš Lalinský Oct 19 '09 at 11:54
  • That was my next question actually, it's here: http://stackoverflow.com/questions/1590648/mysql-innodb-locking-only-the-affected-rows – Chad Oct 19 '09 at 19:41
6

You can use IFNULL:

INSERT INTO users(user_id, name)
SELECT(IFNULL((SELECT MAX(user_id)+1 from users where name='Bob'), 1), 'Bob';
Greg
  • 316,276
  • 54
  • 369
  • 333
  • 1
    looks like it would work but for some reason mysql throws a syntax error: ...syntax to use near 'SELECT MAX(user_id)+1 from users where name='Bob'),1), 'Bob'' at line 1 – Chad Oct 19 '09 at 08:57
  • @Chad: Replace `IFNULL(` with `IFNULL((` and it will work, it's just unbalanced parentheses. – Lukáš Lalinský Oct 19 '09 at 09:02
  • 1
    Yes, that's why it didn't work. I wonder which method is quicker/best. – Chad Oct 19 '09 at 09:08
  • 1
    Oops, fixed the missing bracket. There's very little difference between mine and @Lukas's answers - use whichever you find easier to read / understand. – Greg Oct 19 '09 at 09:33
3

It's been reported as a bug in MySQL.

I'm quoting Guilhem Bichot:

Second, here is the simplest of workarounds:
instead of using:
insert into foo(lfd) values((select max(lfd) from foo)+1)
just do
insert into foo(lfd) select (max(lfd)+1) from foo;

Daan
  • 1,879
  • 17
  • 18
1

Don't use MAX() + 1 then. Use an auto-numbering scheme on the table.

I didn't read the question properly. Use Greg's suggestion of IFNULL().

  • 2
    Won't an auto-numbering scheme on the table number each record sequentially, rather than making the first "Bob" 1 and the second "Bob" 2, and the first "Chad" 1 and then second "Chad" 2 and so on? – David Webb Oct 19 '09 at 08:43
  • 1
    I didn't read the question properly. I'll defer to Greg's answer with IFNULL(). –  Oct 19 '09 at 08:48