Temporary Variable

anjanesh · #1 (**permalink**) 06-20-07, 04:24

Hi

Is there a way to store LAST_INSERT_ID() in a temporary variable and use that variable in the succeeding queries ?

Code:

INSERT INTO `tbl1` VALUES ('', 5, 'data 1', 'data 2');

INSERT INTO `tbl2` VALUES ('', LAST_INSERT_ID(), 'x', 'y');
INSERT INTO `tbl2` VALUES ('', LAST_INSERT_ID(), 'a', 'b');
INSERT INTO `tbl2` VALUES ('', LAST_INSERT_ID(), '1', '2');

Obviously, in the last 2 inserts, this will keep inserting in tbl2, the prev auto-increment values that it just INSERTed in tbl2. I want it to refer to the LAST_INSERT_ID() of tbl1 in all 3 queries (last 3).

Is there a way ?

Thanks

healdem · #2 (**permalink**) 06-20-07, 04:39

If you can use a stored procedure, then yes, otherwise you will have to do it programatically

anjanesh · #3 (**permalink**) 06-20-07, 04:43

I have MySQL 5.0.41. So I can use stored procedures, but is there a way to just store this in a variable without actually creating a function ?

EDIT : solved using SET @lid := LAST_INSERT_ID(); and then
INSERT INTO `tbl2` VALUES ('', @lid, 'x', 'y');

aschk · #4 (**permalink**) 06-20-07, 05:52

Do it all in a transaction set just to be safe...

anjanesh · #5 (**permalink**) 06-20-07, 07:35

Code:

SET @lid:= (SELECT `id` FROM `tbl1` WHERE `uniqueKEY` = '12345');
IF @lid == NULL THEN
   INSERT INTO `tbl1` VALUES ('', '12345', data 1', 'data 2');
   SET @lid := LAST_INSERT_ID();
END IF;

INSERT INTO `tbl2` VALUES ('', @lid, 'x', 'y');
.
.

I get this error message :

Code:

ERROR: Unknown Punctuation String @ 146
STR: ==
SQL: SET @lid := (SELECT `id` FROM `tbl1` WHERE `uniqueKEY` = '12345'); # MySQL returned an empty result set (i.e. zero rows).

Any clue to why @lid == NULL isnt right ?

aschk · #6 (**permalink**) 06-20-07, 07:45

The syntax in SQL is = NOT ==.
That latter is a programming language construct.
i.e.
IF @lid = NULL THEN...

Edit:
In fact come to think of it you're testing NULL, thus
IF @lid IS NULL THEN...

anjanesh · #7 (**permalink**) 06-20-07, 07:54

Code:

IF @lid IS NULL THEN
   INSERT INTO `tbl1` VALUES ('', '12345', data 1', 'data 2');
   SET @lid := LAST_INSERT_ID();
END IF;

Now I get a different error

Code:

#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 'IF @lid IS NULL THEN
   INSERT INTO `tbl1` VALUES ('', '12345', 'data 1' at line 1

aschk · #8 (**permalink**) 06-21-07, 04:28

What is the DDL of your tbl1 ?

Looks like you might be missing a quote
INSERT INTO `tbl1` VALUES ('', '12345', data 1', 'data 2');
but I can't be certain given your error message it appears to have been quoted correctly.

anjanesh · #9 (**permalink**) 06-21-07, 04:44

Apparently, I was trying to do something that cannot be done without it being inside a PROCEDURE. It has been solved. Thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech