FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


mysql insert into multiple tables at once?





ammonkc
how do I write a mysql query to insert into multiple tables at once? I did something like this:

Code:

INSERT INTO table1(c1, c2, c3), table2(c4, c5, c6) VALUES ('v1', 'v2',
'v3',v4, 'v5', 'v6');


but mysql returns an error that says to check the syntax near 'table2(c4,....' . What is the correct syntax for inserting into multiple tables in the same query? any help with this would be greatly appreciated.
Thanks,
dwikristianto
i think that's a weird sql command.
for me,
it wont hurt me much if i separate that sql into two parts and execute them sequentially.

afaik, there's no such db that support that kind of sql.
ChrisCh
Just wondering, why would you want to insert the same information into two different tables within the same database? Just remove one and change your scripts to use that table, rather than having a database double the size of what it could be. Smile
gerpg
You might want to update a user's database and a forum's database at the same time, It's very common to update 2 tables at once when you start to intergrate multiple options on a website.

Louis.
AftershockVibe
It's bad practice to do so, since it defeats one of the design principles of having a database in the first place - a single location for data.

I've never come across a command that will let you do this in one go, which isn't to say it can't be done. It probably just isn't done often because it's considered bad practice! If you are inserting exactly the same information into two tables then at least one of them is redundant.

The simple solution is just to run the command twice and change the table name. Why exactly do you need to do this though? Are you using two extermal systems with different database schemas that your script is trying to interoperate with?
GSIS
I'm not too sure, from the example, precisely what the requirement is.

My understanding is that once you attempt to join tables (use more than one in the same sql statement) they cannot be updated.

My SQL is more than a little out of date but, in pseudocode, this is what I would be planning.

Code:
Start of transaction
 Insert into table1(col1, col2, col3 etc) values v1, v2, v3 etc;
 Insert into table2(col1, col2, col3 etc) values v1, v2, v3 etc;
 If ok then
  commit
 else
  rollback
 ;
End of transaction


Another possibility might be to use a trigger (or whatever equivalent exists in MySQL) so that updating one table fires the trigger causing the other to be updated as well.

If the data is held within the same database I'd be wondering if it hasn't been properly normalised in the first place - perhaps store only a key on one of the tables and use that to reference the other.
Stubru Freak
Look carefully, he isn't trying to insert the same data, but different data into the two tables.
If you want this to assure data integrity, try locking your tables.
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
ammonkc
Stubru Freak wrote:
Look carefully, he isn't trying to insert the same data, but different data into the two tables.
If you want this to assure data integrity, try locking your tables.
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

yes its actually different data. that was only an example that I found in the mysql discussion boards. The query that i wanted to use it for would actually insert different data into 5 separate tables. I was only playing around with this idea because I saw something about it posted on mysql discussion list or something like that. The reason would be for data integrity. I don't want to execute 4 inserts, then have the 5th insert throw an error, resulting in broken data. Would locking my tables be the way to handle this? thanks,
dwikristianto
nope. transaction does.
do rollback when error happen.
ammonkc
Thanks. The database is actually a MyISAM db engine. From what I understand MyISAM is not a transactional db engine. Can rollbacks still be done on MyISAM?
AftershockVibe
You can't do proper rollbacks using MyISAM, you could however implement your own rollback system within PHP after checking whether single queries have failed. This has the problem that any deletions you make afterward aren't guarenteed to work either.

Really, you want to change from MyISAM to something which does support transactions. InnoDB will do this. I assume you're using MySQL and so support for this is actualyl built in, it's just not the default. You'll need to alter your table types to InnoDB.

This should be fairly easy if you have a GUI, if not you can use the SQL command "ALTER TABLE <TableName> TYPE=INNODB"
SH_code
ChrisCh wrote:
Just wondering, why would you want to insert the same information into two different tables within the same database? Just remove one and change your scripts to use that table, rather than having a database double the size of what it could be. Smile


what about backup?
well... for example I use it on one page, where users can write articles, i've got two tables . articles, and articlesBackup, when the article is saved for the first time, it goes to both of them, and until publishing all edits go in both, but any edits after publishing go just to the articles table, so users can click "view original" in case the article was changed after publish (and some parts related to comments changed, or whatever), and user wants to see the unchanged version...
Ryandward
I have two tables, one of companies and one of products.

We are having a problem of when someone updates the name of a company; $cname on the company table how do we update the $cname on the products table simultaneously for products produced by the company.

This should help, right?
Fire Boar
This topic is a bit old. Nevertheless, Ryandward, the solution is to use a constant value to link between items - usually a numeric ID is used, known as a primary key. That way, in "products" it won't be "product 1 produced by company Stuff Inc.", it would instead be "product 1 produced by company 4". You'd then do a join query if you needed to determine the company name.
macky
ammonkc wrote:
how do I write a mysql query to insert into multiple tables at once? I did something like this:

Code:

INSERT INTO table1(c1, c2, c3), table2(c4, c5, c6) VALUES ('v1', 'v2',
'v3',v4, 'v5', 'v6');


but mysql returns an error that says to check the syntax near 'table2(c4,....' . What is the correct syntax for inserting into multiple tables in the same query? any help with this would be greatly appreciated.
Thanks,



im just wondering what is the purpose of doing this... there is a lot of alternatives to achieve insertion on the multiple tables such as using INSERT SELECT statement with JOIN if you want...
octopus
(1) First of all the storage engine is MYISAM, which does not support transactions. Therefore, the problem that may arise with multiple insertion is -
In first table your data has been successfully inserted, but not in second.

Well, this can also be fixed, by creating your own ROLLBACK mechanism

(2) There are times when you really want multiple tables for specific purposes. For instance, you store all your products into one table, and store all comments and view about the products in another table. Because, it is not wise to access the same table repeatedly for stuff like comments and views. So, obviously you maintain a foreign key relation with the products table.

(3) So, now when you JOIN two tables or GROUP BY for getting total views etc., it won't show you the products that has no views. A product without views has no entry in the views table. It is only possible, if you initially created a record for each product into the views table also.

That means, when you insert a product into the product table, you will also have to create a record for that product into the views table with initial views as '0'.
briya1018
Code:

START TRANSACTION;
INSERT INTO table1 VALUES ('1','2','3');
INSERT INTO table2 VALUES ('bob','smith');
COMMIT;


Shocked
manfer
briya1018 wrote:
Code:

START TRANSACTION;
INSERT INTO table1 VALUES ('1','2','3');
INSERT INTO table2 VALUES ('bob','smith');
COMMIT;


Shocked


Annoying.

- A 6 years thread revived once more.
- An answer to the question without paying the minor attention to the discussion. It was already explained that the engine used on the database didn't support transactions.
codersfriend
You can do this with a stored procedure. It is just like a function call in the database
Related topics
script backup database
HTTP AUTH with PHP and mySQL
How To : Improve Your PHP Programming
MySQL DB Backup script - minor problem
A very good PHP MySQL Tutorial
insert data to mySQL database problem
AJAX tutorial [2nd part now updated]
how to insert data into mysql base from a web page
Developing a Login System with PHP and MySQL
a site where i can learn mysql syntax
Data Mining From tables and then insert into MySQL
A variable number of inputs for PHP to insert MYSQL
PHP/MySQL Dropdown value, and insert into database
PHP MYSQL INSERT
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.