Question 21440 in Mysql Question posted in
The official MySQL documentation can be found here.

Which ways is better that insert data to two different table simultaneously in Mysql?

I’m trying to insert data to two different table simultaneously.
If failed , then roll back the data.
And I try to google, and found some solutions:

1.Use data transactions.
2.Create a extra table then join two table.
3.Create a view .

Consider about the web server performance, which one is better?

Tags:

3

Answers


  1. 0

    Use must use your first solution: a transaction. That is the only option. You can do two consecutive INSERT statements in a transaction.

    There is no syntax in SQL to support INSERT to two tables in the same statement.

    You cannot INSERT to a view that is based on a join. Such views are not writeable.

    Login or Signup to reply.
  2. 0

    You shouldn’t do them at the same time, just do one at a time. Even if you made 2 connections and tried to time 2 inserts, MySQL would still make sure to only make 1 change at a time.

    You should just obtain 1 connection, start a transaction, do 1 insert at a time and then commit.

    If either of the inserts fail, both will be reverted.

    Login or Signup to reply.
  3. 0

    You can use a transaction:

    A Transactions are group of SQL statement that are treated as single unit.
    on error it will roll back all SQL statement, to the point where it was started.

    Example:

    START TRANSACTION;
--SQL Statement 1
INSERT INTO table1 (column1, column2, column3) VALUES (value1, value2, value3);

--you can write here another INSERT statement
--SQL Statement 2
UPDATE table2 SET column=column_value WHERE column=value;

COMMIT;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search