Re: multiple table inserts
On Tue, 2006-08-15 at 07:56 -0700, bruce wrote: hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... If your using version 5 you can create a view accross the tables and insert into that. mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table inserts
Hell Bruce Yes ,Here is the solution to insert the values from multiple table into a table . We can achieve this by using the JOINs(INNER JOIN,LEFT JOIN,RIGHT JOIN, join) Consider the tables Animal,Feed and Animalfeed Structure of the animal table Id name variety Structure of the feed table id Feed Structure of animal table id Name feed I wan to update the table Animalfeed from tables Animal and Feed INSERT INTO Animalfeed SELECT a.id,a.name,b.feed FROM Animal a,feed b WHERE a.id=b.id Note: 1.Column Order of animalfeed table and SELECT QUERY must be SAME Datatype 2.If you want to insert the name and feed details into animalfeed table . The query must be follow below synatx INSERT INTO Animalfeed(name,feed) SELECT a.name,b.feed from Animal a,feed b WHERE a.id=b.id Thanks Visolve DB TEAM. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 8:26 PM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table inserts
Hi Bruce U can you mysql_insert_id function to insert values into multiple tables. Please refer to http://www.desilva.biz/mysql/insertid.html for more details. It also illustrates with examples. Thanks Visolve DB Team - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 7:56 AM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: multiple table inserts
hi... you guys misunderstood my question... i don't want to insert into one tbl from multiple tables i want to insert into multiple tables simultaneously using a single query... -Original Message- From: Visolve DB TEAM [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 15, 2006 10:55 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: multiple table inserts Hell Bruce Yes ,Here is the solution to insert the values from multiple table into a table . We can achieve this by using the JOINs(INNER JOIN,LEFT JOIN,RIGHT JOIN, join) Consider the tables Animal,Feed and Animalfeed Structure of the animal table Id name variety Structure of the feed table id Feed Structure of animal table id Name feed I wan to update the table Animalfeed from tables Animal and Feed INSERT INTO Animalfeed SELECT a.id,a.name,b.feed FROM Animal a,feed b WHERE a.id=b.id Note: 1.Column Order of animalfeed table and SELECT QUERY must be SAME Datatype 2.If you want to insert the name and feed details into animalfeed table . The query must be follow below synatx INSERT INTO Animalfeed(name,feed) SELECT a.name,b.feed from Animal a,feed b WHERE a.id=b.id Thanks Visolve DB TEAM. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 8:26 PM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: multiple table inserts
visolve that's not the function/purpose of the mysql_insert_id... that function still requires you to do multiple hits to the db... but thanks for the attempt... -Original Message- From: Visolve DB Team [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 15, 2006 10:59 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: multiple table inserts Hi Bruce U can you mysql_insert_id function to insert values into multiple tables. Please refer to http://www.desilva.biz/mysql/insertid.html for more details. It also illustrates with examples. Thanks Visolve DB Team - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 7:56 AM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table inserts
Bruce, why do you want to do that at all? If you need to add values to several tables either at once or (in case of an error) not at all, you should use transactions. Fred On 8/15/06, bruce [EMAIL PROTECTED] wrote: - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 7:56 AM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table inserts
Hi Bruce What Fred said is true. Only transactions will help you. Regards Visolve DB Team - Original Message - From: Frederic Wenzel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Visolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 1:01 PM Subject: Re: multiple table inserts Bruce, why do you want to do that at all? If you need to add values to several tables either at once or (in case of an error) not at all, you should use transactions. Fred On 8/15/06, bruce [EMAIL PROTECTED] wrote: - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 7:56 AM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]