mysql - Inserting a foreign key, on missing key, update -


i have 2 tables:

   tbl1:      ============================    id     |      token(indexed)    ============================     1     |      2176     2     |      2872     3     |      2881     4     |      1182     tbl2:      =======================    id     |      token_ref    =======================     1     |      2     2     |      3     3     |      1     4     |      1 

in each iteration server receive 'token', , update tbl1 if no token exists, in example token "5241" require insert tbl1.

i need update tbl2 tbl2.id auto_incremented whenever token received (existing or not).
if token new one, first update tbl1, , update tbl2 id of new token.

i thinking on insert on exist update, don't know how combine single command.

to summarize:
need insert on exist update tbl1 in each iteration , insert resulting id tbl2 in single command. possible?

ideas?

i have prepared sqlfiddle of per ondřej's suggestion, may found here.

in schema have proposed following after insert trigger:

create trigger tbl1_ai after insert on tbl1 each row   insert tbl2(token_ref)   values(new.id); 

Comments