Clever Geek Handbook

Foreign key insertion, by missing key, update

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 will receive a "token" and update tbl1

if no token exists, in this example the token "5241"

will need to be inserted into tbl1

.

I need to update tbl2

where tbl2.ID

AUTO_INCREMENT is when whenever a token is received (existing or not).
If the token is new, first refresh tbl1

and then refresh tbl2

with the new token id.

I was thinking about INSERT ON EXIST UPDATE , but I don't know how to combine it into one command.

To summarize:
I need to INSERT ON EXIST UPDATE tbl1

for each iteration, and INSERT

the result ID

in tbl2

the same team. possibly?

ideas?

+3


source to share


1 answer


I prepared a SQLFiddle as suggested by OndΕ™ej which can be found here .

In the circuit, I suggested the following After Insert trigger:



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


source






More articles:


All Articles