Syntax error on DECLARE

Question

I want to use the serial of an insert directly in a next insert.

Based on Help with T-SQL script: Insert record, then use identity of that insert on another statement? I execute the following SQL query:

DECLARE @Id1 BIGINT
DECLARE @Id2 BIGINT
INSERT INTO doses (CPS, ground, total) VALUES (10, 10, 10)
SET @Id1 = SELECT SCOPE_IDENTITY()
INSERT INTO places (x, y, z, speed) VALUES (10, 10, 10, 10)
SET @Id2 = SELECT SCOPE_IDENTITY()
INSERT INTO measurements (time, place, note, dose, id_dataset) VALUES ('Test', @Id1, 'test', @Id2, 17)

This give me a syntax error and I can't find anything wrong with my DECLARE.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @Id1 BIGINT
DECLARE @Id2 BIGINT INSERT INTO doses (CPS, ground, total)' at line 1 

It seems that DECLARE in MySQL can only be used in a BEGIN..END. http://dev.mysql.com/doc/refman/5.0/en/declare.html

Also SCOPE_IDENTITY is replace with LAST_INSERT_ID().

This is my new query:

BEGIN

DECLARE @Id1 BIGINT;
DECLARE @Id2 BIGINT;

INSERT INTO doses (CPS, ground, total) VALUES (10, 10, 10);
SET @Id1 = (SELECT LAST_INSERT_ID());

INSERT INTO places (x, y, z, speed) VALUES (10, 10, 10, 10);
SET @Id2 = (SELECT LAST_INSERT_ID());

INSERT INTO measurements (time, place, note, dose, id_dataset);
VALUES ('Test', @Id1, 'test', @Id2, 17);

END

But I still get the same error.

Answer 1

It seems you cannot declare and use variables outside triggers/procedures/functions or events. So you can also not work around this by just writing BEGIN...END around it.

Since you need two different ID values in one select-statement you could either wrap this into a stored procedure (that should circumvent the issue of not being able to declare variables) or just use the MAX-value of ID in both columns in your final insert-statement. (this of course assumes you have auto_increment enabled or at least ascending IDs in both tables)

So in essence you should be able to make it work if you replace your whole code with:

INSERT INTO doses (CPS, ground, total) VALUES (10, 10, 10);

INSERT INTO places (x, y, z, speed) VALUES (10, 10, 10, 10);

INSERT INTO measurements (time, place, note, dose, id_dataset)
VALUES ('Test', (SELECT MAX(ID) FROM doses), 'test', (SELECT MAX(ID) FROM places), 17);

You should also wrap this in an transaction to be safe that no other IDs have been inserted in the meantime.