Search code examples
mysqllast-insert-id

Duplicate AUTO_INCREMENT Id upon insertion into another column?

Say I have this table structure:

CREATE TABLE bananas (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  CHAR(127),
    order INT
);

And I want the initial order field of the rows to match insertion sequence so, order == id

Now I can make the insert with some dummy value, check what ID it got and then update the row but this seems dumb. How do I make this happen upon insert? 

INSERT INTO bananas (name, order) VALUES ($name, ???);
Solution

  • Immediatly after running an INSERT, you can use function LAST_INSERT_ID to recover the id of the last successfully inserted record and run an UPDATE query :

    INSERT INTO bananas (name, ord) values ('foo', 100);
UPDATE bananas SET ord = LAST_INSERT_ID() WHERE id = LAST_INSERT_ID();

    This DB fiddle demonstrates how this works.

    PS : column name order clashes with the corresponding SQL keyword, I renamed that column ord in the code.