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, 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.
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. 
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.
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?
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. 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, nope. transaction does.
do rollback when error happen.
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?
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"
