CODEWITHSUNDEEP
mysqlinsertforeign-keysprimary-keyauto-increment
Jon8672
Jon8672

Reputation: 63

SQL insert auto increment value

I am currently working on a database that stores statements in 2 tables, and has another table linking statements together. The first 2 tables auto increment the primary ID on every entry, however I need to know the new ID's that have been given to enter it into the table that links them.

Table 1
SID     | Statement         | Language
1   | Have a cup of coffee      | English
2   | Have a cup of green tea       | English

Table 2
AID | Action
1   | recycle the cup
2   | feel full of cafine
3   | throw away the cup
4   | feel healthy
5   | jump

Table 3 - relationships

SID | AID
1   | 1
1   | 2
1   | 3
2   | 1
2   | 3
2   | 4

so an example would be:

INSERT INTO actions(Action) VALUES ('Go to the pub');
INSERT INTO statements(statement, Language) VALUES ('Have a pint', 'English');

the relationships would then be, knowing the auto increment values for this example would be 3 and 6:

INSERT INTO Relationships(SID,AID) VALUES (3,6);

I need the values 3 and 6 need to be inserted as variables, as a statement such as:

INSERT INTO Relationships(SID,AID) VALUES (id1, id2);

Upvotes: 1

Views: 3999

Answers (1)

rs.
rs.

Reputation: 27467

Try this

INSERT INTO actions(Action) VALUES ('Go to the pub');
SET @aid = LAST_INSERT_ID();

INSERT INTO statements(statement, Language) VALUES ('Have a pint', 'English');
SET @sid = LAST_INSERT_ID();

INSERT INTO Relationships(SID,AID) VALUES (@sid,@aid);

Upvotes: 1

Related Questions