Bruce,
Sorry, I guess I wasn't clear. I understood what you were asking, and I thought I answered it. Your search of the mysql docs and google found nothing about multiple-table inserts because you can't do that. I think the mysql manual page I referenced is clear:
INSERT Syntax
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
See? Multiple columns, but only one table.
My intent in my second paragraph was to offer help. Since there is no multi-table insert, you need to solve your problem a different way. You'll probably need a separate insert for each table, and possibly a temporary table. I didn't want to guess what you need, however, based on your example, because I wasn't sure what you really wanted to accomplish.
I suppose it might have been more helpful to offer a couple of general suggestions. Here goes:
INSERT INTO table1 SELECT id, a.q1, b.q1 FROM a JOIN b ON a2.t=a1.t WHERE a2.r='4';
INSERT INTO table2 SELECT id, a.q2, b.q2 FROM a JOIN b ON a2.t=a1.t WHERE a2.r='4';
Of course, that leaves a possibility that table a or b gets changed in between, and running the JOIN twice may not be all that efficient. This might be better:
CREATE TEMPORARY TABLE holding SELECT id, a.q1 aq1, a.q2 aq2, b.q1 bq1, b.q2 bq2 FROM a JOIN b ON a2.t=a1.t WHERE a2.r='4';
INSERT INTO table1 SELECT id, aq1, bq1 FROM holding; INSERT INTO table2 SELECT id, aq2, bq2 FROM holding;
DROP TABLE holding;
If that doesn't help you with what you need, reply with a description of the problem to be solved, and I'm sure someone will have a solution.
Finally, about my P.S. There are many, many questions on this list that come down to the poster not knowing/understanding JOIN vs. LEFT JOIN. They either need a LEFT JOIN, or they always use LEFT JOIN even when they don't need it. Perhaps you do not fall into that category, and it is perfectly obvious to you that "WHERE a2.r='4'" throws away any extra (null) rows created by the LEFT JOIN, making the LEFT JOIN equivalent to, but slower than, a simple JOIN.
Michael
bruce wrote:
michael...
it was meant as an example, to convey what i want to do, which is do a simltaneous insert into multiple tables at the same time. the syntax concerning the left join/elements to be inserted was not intended to be syntacticly (sp?) correct!!!
and as i stated, searching through mysql docs/google didn't shed light on this issue. could be because mysql doesn't permit this kind of interaction, although it is permitted with the update...
does this make more sense to you now...
-regards....
-----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED]
bruce wrote:
hi...
trying to figure out how to structure an insert/select for a multiple
table
situation...
sort of... insert table1, table2 (table1.item1, table1.item2, table2.item1,...) select a.q1, b.q2 from a1 left join a2 on a2.t=a1.t where a2.r='4';
i can't seem to figure out the syntax for the multiple table insert...
searching through google/mysql hasn't shed any light on this..
thanks for any pointers/comments...
-bruce
That's because there is no multiple-table INSERT syntax. See the manual for correct INSERT syntax <http://dev.mysql.com/doc/mysql/en/INSERT.html>.
I can't tell from your example what you are trying to do. You only select 2 columns, but you seem to be trying to insert the result into at least 4 columns. Perhaps if you carefully described what you have and what you want, someone will be able to suggest a way to accomplish your goals.
Michael
P.S. You don't need a LEFT JOIN in that example. A simple JOIN would do.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
