1

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);
jschoi
  • 1,884
  • 1
  • 12
  • 26
Jon8672
  • 63
  • 2
  • 5

1 Answers1

1

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);
rs.
  • 26,707
  • 12
  • 68
  • 90
  • Thanks for the reply! However when I run this code I get:Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare v_aid integer, v_sid integer' at line 1 – Jon8672 Apr 18 '13 at 22:24
  • I was wondering if you knew how to declare this in SQLite code as well? – Jon8672 Apr 18 '13 at 22:50