0

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
kimondoe
  • 567
  • 2
  • 9
  • 27
  • Possible duplicate of [How to get the next auto-increment id in mysql](http://stackoverflow.com/questions/6761403/how-to-get-the-next-auto-increment-id-in-mysql) – CollinD Oct 05 '15 at 13:14
  • Question is not clear . – Janny Oct 05 '15 at 13:14
  • I want a query that will result to my 'result of query' – kimondoe Oct 05 '15 at 13:24
  • 1
    you can create a stored procedure, best ya got. One call all in one swoop. Otherwise depending on your programming interface, you can make a subsequent call to get the id just inserted. (you certainly don't need the other data, as you just gave it) – Drew Oct 05 '15 at 13:26

2 Answers2

1

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.');
vhu
  • 12,244
  • 11
  • 38
  • 48
0

You can do that with Max Id + 1.

Maybe this topic will help you out.

php mysql insert (max(id)+1)

Community
  • 1
  • 1
Puya Sarmidani
  • 1,226
  • 9
  • 26