Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: Temporary Variable

    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
    MySQL 5.1

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you can use a stored procedure, then yes, otherwise you will have to do it programatically
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    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');
    Last edited by anjanesh; 06-20-07 at 06:21.
    MySQL 5.1

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Do it all in a transaction set just to be safe...

  5. #5
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    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 ?
    MySQL 5.1

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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...

  7. #7
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    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
    MySQL 5.1

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  9. #9
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Apparently, I was trying to do something that cannot be done without it being inside a PROCEDURE. It has been solved. Thanks.
    MySQL 5.1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •