retrieving and using the auto-increment value WITHIN a query

Question

My problem relates to retrieving and using the auto-increment value WITHIN a query

I have three tables, tab1, tab2 and tab3.

tab1 has a 1 to 1 relationship with tab3 (non-TNF I know, but necessary due to timing issues).

tab2 has a many to one relationship with tab3.

In the past, tab3 DID NOT have entries when there was a one to one relationship between tab1 and tab2. I want to create the missing records in tab3. tab1 and tab2 contain a foreign key field tab3_id.

Based upon a common value for col2 in tab1 and tab2, (it is not that simple in practice but will do for this question) I can easily create the records in tab3 with

INSERT INTO tab3 (tab1_id, tab1_value, tab2_id, tab2_value) 
   SELECT
     tab1.col1 AS tab1_id, 
     tab1.col2 AS tab1_value,
     tab2.col1 AS tab2_id, 
     tab2.col2 AS tab2_value
     FROM  `tab2` 
     INNER JOIN tab1 
     ON tab1.col2 = tab2.col2

(for simplicity I have left out the check to ensure tab3 does not already have a linking record in it)

But I do not know how to capture the auto-incremented value of the primary key (id) of tab3 in order to update tab1 and tab2. I have searched the MYSQL documentation, Stack Overflow and googled more generally, but found nothing.

Is it actually possible to slot an update statement somewhere in the above and capture LAST_INSERT_ID() and use it in the update.

score 0 · Answer 1 · answered May 08 '13 at 13:13

I think the only way would be to insert a placeholder row at beginning and then update that row together with the other updates

insert into tab3 set tab3_id = null;
set @tab3id = (select last_insert_id();)

update tab3,
    tab2 
    inner join tab1 on tab1.col2 = tab2.col2 
    set tab3.tab1_id = tab1.col1, tab3.tab1_value=...
    , tab1.tab3_id = @tab3id
    where 
   tab3.id = @tab3id
;

retrieving and using the auto-increment value WITHIN a query

1 Answers1