Can we insert data in multiple table with single query in mysql

[aruti]

Please tell me can we insert data in multiple table with single query in mysql.

For example i have two table with name `table1` and `table2` and fields in `table1` are 'id' auto incremented and primary key, second field is 'name' data type varchar of 25 and another table `table2` have fields 'id' auto incremented and 'address' data type varchar 55.

I want to know is it possible to add the data in the fields of `table1` and `table2` with single insert query

[urtrivedi]

I dont think so.

[MeSampath]

Hi,

MySQL doesn't allow us to do that.

But as a work around, we can do by having/combining multiple sql statements separated with semi colon.

for more detail of insert syntax, have a look into;
http://dev.mysql.com/doc/refman/5.0/en/insert.html

Good luck.

[baki100]

you can build a anonymous block or procedure to do this as well

[aruti]

Hello,

Thanks a lot.....

I dont think so.

[rch1231]

baki100 was right, the best way to update multiple table is with a procedure. Procedures allow you to use multiple MySQL statements separated by a ;. You can pass fields into a procedure and will probably need to declare some variables to hold data temporarily. below is a sample of a procedure I use to update several tables after a record is created. I call the procedure like this:

call addserver(new.server_id, new.label, new.rack_id, new.client_id, new.Number_PSU)

and here is the actual procedure:

begin
declare SOID INT;
declare ROID INT;
declare COID INT;
declare PSUOID INT;
declare PSUPOID INT;
declare NICOID INT;
declare NICPOID INT;
insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)
values(1 , SID, SLAB, now());
set SOID=Last_Insert_ID();
SELECT ObjIDClient.object_auto_id
FROM ObjIDClient
WHERE ObjIDClient.client_id = CID into COID;
SELECT ObjIDRack.object_auto_id
FROM ObjIDRack
WHERE ObjIDRack.rack_id =  RID into ROID;
insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)
values(26 , 0, 'PSU', now());
set PSUOID=Last_Insert_ID();
insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)
values(43 , 0, 'PSU Power Port', now());
set PSUPOID=Last_Insert_ID();
insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)
values(16 , 0, 'NIC', now());
set NICOID=Last_Insert_ID();
insert into`object_objects` (`parent_object_id` ,  `object_id`)
values(ROID, SOID),(COID, SOID),(SOID,PSUOID),(SOID,NICOID),(PSUOID,PSUPOID),(20127,PSUPOID);
IF NPSUP = 2 Then
call AddPSUPort(SID);
end if;
end