1

I've just got a situation where i need to duplicate the unique (auto-incremented) id of each new row in another column. So I was wondering, instead of doing it the old fashioned way:

  1. Insert row
  2. Get insert_id
  3. Update row with the insert_id

Can I tell MySQL to use the same value to which the new id is going to be set, as a value for another column?

Something along the line of this:

INSERT INTO my_table(unique_column, id_duplicate) 
VALUES('value', GET_UNIQUE_ID_OF_THIS() )
darma
  • 4,687
  • 1
  • 24
  • 25
3Nex
  • 517
  • 6
  • 14
  • 1
    You can never know what the next ID is going to be, unless you lock it .. by inserting a new record, hence doing it your "old fashioned way". – darma Aug 28 '12 at 12:15
  • Why are you duplicating the auto-increment column in another column? If you describe what you're trying to accomplish, we can help you with the best practice or a different solution. – Marcus Adams Aug 28 '12 at 12:25
  • I don't know if I can explain it well, it's kind of complicated.. But basically there's a database where a widget stored in one table (A) refers to some context in a different table (B). And now we introduced a new widget type which has to reference a group of rows (in B) instead of just one. So I didn't want to make this group ID (B) an incrementation of the latest max group ID, because it would be vulnerable to the mutual exclusion problem. But instead I wanted to use the widget's unique ID (from A) as the context ID in table A and thus the group ID in table B. That is my simplest solution. – 3Nex Aug 28 '12 at 12:39
  • What is this "mutual exclusion problem"? – Marcus Adams Aug 28 '12 at 12:50
  • http://en.wikipedia.org/wiki/Mutual_exclusion – 3Nex Aug 28 '12 at 13:05

1 Answers1

0

There's no way to accomplish what you're trying to accomplish using an auto-increment column in a single query.

Try approaching it from the other direction. Let's say your auto-increment column is id and your other column is called other_id. You could set the other_id column to allow NULL and simply set its value to NULL for the initial insert.

Then, when you query the value, simply do something like this:

SELECT id, IFNULL(other_id, id) AS other_id FROM mytable

The net result is the same. If you have something against NULL, you could also use 0 as the default value since auto-increment values start at 1.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • Oh, this is very good, and perhaps it'll also make the table (perhaps insignificantly) smaller. Thank you very much! – 3Nex Aug 28 '12 at 12:42
  • Actually, let me ask you one more question before I use it. Assuming a table big enough in size that the difference would be noticable, would I rather sacrifice the table size by using my "old-fashioned" way or query speed by using yours? The select queries are much more often used than the insert one, so my previous longer insert query is less important than this select one. – 3Nex Aug 28 '12 at 12:46
  • The above solution prevents MySQL from using an index on the other_id column, so perhaps you should go ahead and use `LAST_INSERT_ID()` if it's not a problem issuing more than one query (the "old fashioned way") when you insert new rows. – Marcus Adams Aug 28 '12 at 12:49