1

I have three datatables, tableA, tableB, and tableAB. The contents aren't really important, just that tableA and tableB have primary keys and tableABrelates the keys in the two tables. The basic structure is below:

aId|data ---+---- 1 |4 2 |83 bId|data ---+---- 1 |a 2 |cd 3 |bf abId|aId|bId ----+---+--- 1 |1 |1 2 |1 |2 3 |2 |3

What I'd like to do is combine the inserts of all three of these tables into one query, but am not sure how to. The current idea that I'm working with is below, but it does not work. Important things to note are aId's may refer to multiple bId's, but each bId will only have one aId referring to it. As such, aId's may not refer to existing bId's. The big things I am struggling with are 1) making the value of an insert come from another insert, and 2) simulating multiple inserts, for bId's, in a single query.

Current query:

insert into tableAB(aId, bId) values((select aId from(insert into tableA(data) values(5))), (select bId from(insert into tableB(data) values("f"))));

I'm really not sure multiple inserts in a single query is possible and don't know a way of writing it above.

Improve this question
4
  • Multi-table inserts in a single query are not supported by MySQL. :( Commented Mar 29, 2016 at 16:49
  • You could do what you want in Postgres, but MySQL does not support inserts into multiple tables in a single query. Commented Mar 29, 2016 at 16:49
  • There is a LAST_INSERT_ID, see the manual, sample: sqlfiddle.com/#!9/a8102/1/0 Commented Mar 29, 2016 at 16:50
  • INSERT queries don't return a value, you can't use them as subqueries in a SELECT. Commented Mar 29, 2016 at 17:23

2 Answers 2

Reset to default
1

Use LAST_INSERT_ID() to get the IDs of the first two inserts.

INSERT INTO tableA (data) VALUES (5); SET @idA = LAST_INSERT_ID(); INSERT INTO tableB (data) VALUES ('f'); SET @idB = LAST_INSERT_ID(); INSERT INTO tablAB(aId, bId) VALUES (@idA, @idB);

You could put this all into a stored procedure.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I had forgotten about LAST_INSERT_ID(), thank you for your answer
1

You can't do that in a single query.

But you could use a TRANSACTION instead:

START TRANSACTION; INSERT INTO tableA(data) VALUES(5); SET @aid = LAST_INSERT_ID(); INSERT INTO tableB(data) VALUES('f'); SET @bid = LAST_INSERT_ID(); INSERT INTO tableAB(aId, bId) VALUES(@aid,@bid); COMMIT;

LAST_INSERT_ID() returns the id of previously inserted row. It is then stored in a user variable using SET and can thereby be referenced in following statements.

Improve this answer

7 Comments

Isn't this the same as @Barmar's answer, except with rollback control? It seems, specifically to my question, your answer and his are functionally the same
@yanman1234 He posted only a couple of minutes after me, so he could have been working on it at the same time.
And using a transaction for related queries like this is a good idea.
I didn't mean for it to sound like an accusation of copying or anything, I was just trying to see if there was more to his answer in using transaction or if fundamentally both the answers were equivalents. I've personally never used transaction before which is why I asked.
When you use a TRANSACTION you make sure that none of the changes become permanent until COMMIT is executed. If say your server crashes after the first INSERT statement you don't end up with rows in tableA with no relations to tableB where your application might expect at least one. You wanted to perform all inserts in a single query so I expected that this behaviour was what you sought.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.