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?
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);