SQL relational insert to 2 tables in single query without resorting to mysql_insert_id()

Question

I'm not totally new to SQL, but am rusty and struggle with MYSQL (using PhPMyAdmin)... looked at this: MySQL insert to multiple tables (relational) and some other related topics, but haven't found an answer. I'm simplifying my example to get the point across.

If you have two tables:

'table1' has: id (primary key, auto-increment), description (text)
'table2' has: id (primary key, auto-increment), title (text), description_id (int)

How do I create a singe INSERT statement so that description_id is storing the value for table1.id ?

I know there are php ways to do 2 queries but I'd like to do it all in SQL, there must be a way? Should I set up my tables differently? I read something about foreign keys and have no idea if that's applicable.

Thanks!

http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization — hackattack, Jul 19 '12 at 20:25
I'm coming across something promising on the mysql docs: `INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); # use ID in second table` — tim, Jul 19 '12 at 20:26

score 5 · Accepted Answer · edited May 23 '17 at 10:34

5

thanks to @hackattack, who found this ? answered already elsewhere.

BEGIN
INSERT INTO users (username, password) 
  VALUES('test', 'test')
INSERT INTO profiles (userid, bio, homepage) 
  VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;

BUT, ALAS - that didn't work. The MySQL 5 reference shows it slightly different syntax:

INSERT INTO `table2` (`description`) 
  VALUES('sdfsdf');# 1 row affected.
INSERT INTO `table1`(`table1_id`,`title`) 
  VALUES(LAST_INSERT_ID(),'hello world');

And, lo/behold - that works!

More trouble ahead Although the query will succeed in phpMyAdmin, my PHP installation complains about the query and throws a syntax error. I resorted to doing this the php-way and making 2 separate queries and using mysql_insert_id()

I find that annoying, but I guess that's not much less server load than a transaction.

edited May 23 '17 at 10:34

Community

1
1

answered Jul 19 '12 at 20:30

tim

3,823
5
34
39

Exactly what [I proposed](http://stackoverflow.com/a/11568744/215042) (Thanks for voting up or accepting answer...). Strictly speaking though, this is still 2 queries. It's just that they're being executed as one single, atomic, action. – RobIII Jul 19 '12 at 20:32
@roblll aha, I learned something new from your comment - an atomic action... well at the end of the day its about efficiency and server load etc... so whatever makes it tighter and faster, that's what I was after. – tim Jul 19 '12 at 20:53
@RobIII and How is that different from my suggested alternative in PHP? – hjpotter92 Jul 19 '12 at 20:57
@tim Transactions might actually slow things down and you have to take into account the 'penalties' you're paying for them. Having said that; I'd use them. – RobIII Jul 19 '12 at 20:58
@Nerd-Herd [My answer](http://stackoverflow.com/a/11568744/215042) was/is: **You can't**. Then I suggested using a transaction to make it at least (on the application level) seem to be an atomic action. You could still supply all the values and do "one" insert. Your answer assumes data already in one or more tables. Actually, I don't see in what way my answer ("you can't") resembles [yours](http://stackoverflow.com/a/11568801/215042)? – RobIII Jul 19 '12 at 20:59

RobIII · Answer 2 · 2012-07-19T21:04:18.893

1

You can't insert in more than one table (writeable views aside*, but AFAIK MySQL doesn't support them) with an insert statement.

What might be of interest for you are transactions which allow you to 'group' statements into one single "atomic" action.

^{_{* Which, in fact, only would allow updates, not inserts (I believe, but now I'm starting to doubt it... anyways, it's not the issue here anyway)}}

edited Jul 19 '12 at 21:04

answered Jul 19 '12 at 20:24

RobIII

8,488
2
43
93

score -1 · Answer 3 · answered Jul 19 '12 at 20:25

-1

You probably need to use Triggers here so that whenever table2 gets an insert operation, a trigger executes to do an insert on table1.

answered Jul 19 '12 at 20:25

abhi.gupta200297

881
6
12

And how would you supply the values for the fields of the second table to this trigger? – RobIII Jul 19 '12 at 20:26

SQL relational insert to 2 tables in single query without resorting to mysql_insert_id()

3 Answers3