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!

Answer 1

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.

Answer 2

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)}}