inserting into more than one table : mysql

Question

Lets say we have v1, v2, v3 and v4 values of which, v1 and v2 are to be inserted into table t1; v3 and v4 into table t2.
we can usually do that using:

INSERT INTO t1 VALUES(v1,v2) INSERT INTO t2 VALUES(v3,v4)

However, if we want to do that in a single statement?

something like INSERT INTO t1 VALUES(v1,v2), t2 VALUES(v3,v4)

someone asked me to use transactions to achieve that, but i don't know if that works for MyISAM.

I am using MySql database.

Individual statements in MyISAM tables are transactional: dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html — EthanB
– EthanB, Commented Aug 24, 2012 at 13:54

EthanB · Accepted Answer · 2012-08-24 14:01:17Z

Sorry, only one table per INSERT statement: http://dev.mysql.com/doc/refman/5.5/en/insert.html

You could potentially change the engine to InnoDB with ALTER TABLE, but make sure it won't break anything:

ALTER TABLE t1 ENGINE = InnoDB;

One final possibility (if you're really desparate to use transactions) would be to denormalize the data--merge t1 and t2 into the same table. But I'd recommend InnoDB, first.

thank you. I'll have to change the engine to InnoDB and use transactions then. :)

Collectives™ on Stack Overflow

inserting into more than one table : mysql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related