Insert into two tables.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jim_73_mk1
Forum Newbie
Posts: 15
Joined: Mon Nov 24, 2003 9:42 pm
Contact:

Insert into two tables.

Post by jim_73_mk1 »

Simple question but not one I have been able to find an answer for in any documentation or website. When doing a SELECT in MySQL you can join tables and retrieve data from both at the same time. My question is can this same thing be done, joining two tables, when doing an INSERT thereby being able to insert data into both tables at the same time?

Start the bullets flying!

Jim
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

You can make a temp table, in mysql (MERGE). Witch is created from 2 or more other tables even with a selection.
But i don't think you can insert into that one.

Please refer to mysql manual. look for merge tables.
cggreer
Forum Newbie
Posts: 14
Joined: Mon Jul 21, 2003 6:39 am

Post by cggreer »

Using the SELECT statement, you can read values from multiple tables at once. INSERT statements, however, are restricted to updating specified columns and/or records in a single table.

If you are trying to insert or update data into two tables you will need two separate statements. This is where transactions can be very handy - unfortunately MySQL doesn't support them unless you're using the InnoDB transactional storage engine.

Here's a simple rundown on what transactions are good for - not necessarily related to the question, but good background info.
The default engine for MySQL is MyISAM - which handles statements as atomic operations. That is, each statement (each of your two INSERT statements, for example) are executed separately and automatically committed to the database. One statement may succeed, but the other may fail. This can be a problem if you know that both INSERTs need to be successful, or neither can be (think of it logically as an AND, rather than an OR).
Transactions allow you to execute a series of statements and, basically, if one fails, they all fail.

Transaction-based storage engines do have processing overhead - so if you don't absolutely need them, don't use them.

Links for reference:
MySQL INSERT Statement: http://dev.mysql.com/doc/mysql/en/INSERT.html
MySQL Transaction info: http://dev.mysql.com/doc/mysql/en/ANSI_ ... tions.html
Post Reply