Inserting a foreign key, on missing key, update

Question

I have two 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 the server would receive a 'token', and would update tbl1 if no token exists, in this example token "5241" would require an insert into tbl1.

I need to update tbl2 where tbl2.ID is AUTO_INCREMENTed whenever a token is received (existing or not).
If the token is a new one, first update tbl1, and only then update tbl2 with the id of the new token.

I was thinking on the INSERT ON EXIST UPDATE, but I don't know how to combine it into a single command.

To summarize:
I need to INSERT ON EXIST UPDATE tbl1 in each iteration and INSERT the resulting ID into tbl2 in a single command. is that possible?

ideas?

Do you need to keep these in separate tables? It would be much cleaner to have one table with `ID|TOKEN|TOKEN_REF` and you could use an insert with an `ON DUPLICATE KEY UPDATE` clause to increase the ref counter. — fejese, Jul 21 '15 at 07:56
alas, there are other tables that use the token reference ID as well — Ted, Jul 21 '15 at 10:28
are triggers an option? then you could do an insert on `tbl1` ignoring if there's already an entry and create an entry in `tbl2` with `token_ref=0` with a trigger if needed and then always run the update. Might not be the most efficient but does what you're asking :) — fejese, Jul 22 '15 at 20:25
MySQL can't insert into two tables using one single command, you should use trigger as @fejese mentioned or create stored procedure for this operation. — Ondřej Šotek, Aug 15 '15 at 06:01

score 0 · Answer 1 · answered Aug 18 '15 at 15:28

I have prepared an SQLFiddle of as per Ondřej's suggestion, which may be found here.

In the schema I have proposed the following After Insert trigger:

CREATE TRIGGER tbl1_ai AFTER INSERT ON tbl1
FOR EACH ROW
  INSERT INTO tbl2(TOKEN_REF)
  VALUES(new.ID);

Inserting a foreign key, on missing key, update

1 Answers1