Returning Query_ID from insert statement

Question

I am trying to record the duration details of query's into a table. The issue i am having is with Line 8. This is because i need a way of returning what QUERY_ID is created from the lines 2 & 3?

1)Set profiling =1;

2)INSERT INTO Master
3)VALUES ("a12t22h@hotmail.com","efheif","pwoe");

4)INSERT INTO Duration(Status,DURATION)
5)SELECT STATE, FORMAT(DURATION, 6) AS DURATION
6)FROM INFORMATION_SCHEMA.PROFILING;
7)SELECT * FROM INFORMATION_SCHEMA.PROFILING
8)WHERE QUERY_ID = 1;

Thanks

http://stackoverflow.com/questions/3837990/last-insert-id-mysql — ryan1234, Feb 19 '13 at 18:40

score 0 · Answer 1 · answered Feb 19 '13 at 18:46

0

I'm guessing you want return the Id of the value inserted on line 2? You can grab the inserted Id by using ;

SELECT LAST_INSERT_ID();

I'd assign that to a variable and then use it in your last insert statement.

answered Feb 19 '13 at 18:46

Robb

86
3

I have just tried SELECT LAST_INSERT_ID(); but it returns the value 0. Any ideas? thanks – edited Feb 19 '13 at 18:58

score 0 · Accepted Answer · answered Feb 19 '13 at 19:07

0

try this. it is a temp variable and has to be used on the next line after the insert or the value will be reset.

Set profiling =1;

INSERT INTO Master
VALUES ("a12t22h@hotmail.com","efheif","pwoe");

SET @v1 = LAST_INSERT_ID()

INSERT INTO Duration(Status,DURATION)
SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING;
SELECT * FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = @v1;

answered Feb 19 '13 at 19:07

JParadiso

49
2

Thanks just need find out why its always returning as Zero now. – edited Feb 19 '13 at 19:15
Did you set Query_Id to auto increment when defying the column? QUERY_ID mediumint NOT NULL AUTO INCREMENT – JParadiso Feb 19 '13 at 19:24

Returning Query_ID from insert statement

2 Answers2