Search code examples
mysqlinsert-into

Inserting an entry to its table using the auto incremented ID as part of the value

Is it possible to use only one query to insert an entry to a table with one of its columns needs to use the entry's ID

example table schema:

tbl_post:
 col_id: (auto-increment)
 title: string
 body: string
 guid: string

example query:

INSERT INTO tbl_post(title, body, guid)
VALUES("sample title", "sample body", CONCAT("http://site/?id=", col_id))

result of query:

col_id | title        | body        | guid
-------+--------------+-------------+--------------
1      | sample title | sample body | http://site/?id=1
Solution

  • Unfortunately this is not possible; you can't use auto increment value for populating other columns in single INSERT.

    In general best option is to run a single transaction that performs the INSERT followed by UPDATE:

    START TRANSACTION;

INSERT INTO tbl_post(title, body)
VALUES("sample title", "sample body");

UPDATE tbl_post SET guid=CONCAT("http://site/?id=", col_id) 
 WHERE col_id=LAST_INSERT_ID();

COMMIT;

    This is guaranteed to be atomic on InnoDB table so it either succeeds or fails completely. You are out of luck with MyISAM as it doesn't support transactions.

    As @Drew pointed out in comments, you may roll this into a stored procedure:

    CREATE PROCEDURE insupd (ttitle VARCHAR(32), tbody VARCHAR(16))
BEGIN
 START TRANSACTION;
 INSERT INTO tbl_post(title, body)
  VALUES(ttitle, tbody);

 UPDATE tbl_post SET guid=CONCAT("http://site/?id=", col_id) 
  WHERE col_id=LAST_INSERT_ID();
 COMMIT;
END;

    Once that's done, you simply use the following to insert data:

    CALL insupd('yourfancytitle','blah.');