Re: insert/select for multiple tables...
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]
RE: insert/select for multiple tables...
michael... it was meant as an example, to convey what i want to do, which is do a simltaneaous 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] Sent: Wednesday, September 01, 2004 10:06 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: insert/select for multiple tables... 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert/select for multiple tables...
At 10:52 -0700 9/1/04, bruce wrote: michael... it was meant as an example, to convey what i want to do, which is do a simltaneaous 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... You can't do it. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert/select for multiple tables...
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]
