0

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

5
Contributors
5
Replies
19
Views
7 Years
Discussion Span
Last Post by rch1231
0

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.