Will it be possible to insert into two tables with same insert command?
+6
A:
No you cannot perform multiple inserts into two tables in one query.
Anthony Forloney
2010-02-03 23:24:17
+1
A:
Maybe in a future release of MySQL you could create a View containing the 2 tables and insert into that.
But with MySQL 5.1.41 you'll get the error:
"Can not modify more than one base table through a join view"
But inserting into 2 tables with 1 query is a weird thing to do, and I don't recommend it.
For more on updatable views check out the MySQL reference.
Bob Fanger
2010-02-03 23:39:25
It is not weird to want to update (or insert into) multiple tables as part of a single atomic (i.e. all or nothing happens) operation. The more normalised your database, the more likely the need. You just need to protect the multiple updates in a db transaction.
Craig Young
2010-02-04 01:09:06
True, but when I see a *single* insert statement, i will assume only 1 table is affected. The "weird" part applies to the 1 query aspect.
Bob Fanger
2010-02-04 01:20:03
+2
A:
No you can't.
If you want to ensure the atomicity of an operation that requires data to be inserted into 2 tables, you should protect it in a transaction. You either use the SQL statements BEGIN TRAN and COMMIT TRAN, or you use a transaction boundary in whatever language you're using to develop the db access layer. E.g. something like Connection.StartTransaction and Connection.Commit (or Connection.Rollback on an error).
Craig Young
2010-02-04 01:04:53