MULTIPLE INSERTS 2

[lazytrucker]

Is it possible to simultaneously insert a row into different tables using different column names in each table and different/similar values.

EG:

INSERT INTO table1 (col1, col2, col3) values (val1,val2,val3)

INSERT INTO table2 (col5, col6, col7 ,col8) value(val1,val2,val6,val8)

(BOTH above inserts must be carried out at the same time)

This would process both inserts, and if one insert failed then the other would fail also? Similar to transactions in MS SQL Server.

Cheers LazyTrucker.

 

[r937]

sure, you just need to be on version 5

rudy

http://r937.com/

SQL Consulting

 

[ericbrunson]

Do you mean they need to be carried out in the same transaction? Because they can't be carried out at the same time, even with a multi-table insert.

If you want to disable autocommit mode for a single series of statements, you can use the START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.