insert/select for multiple tables...
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 -- 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 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]
another insert/select for multiple tables... (or, determining previous auto-increment ID)
The question:
Is there anything in MySQL that will allow me to determine, accurately, the
last auto-incremented field from a particular database, so I can then insert
based upon this into another table? What if 2 users input at the same time?
Please see the 'long explanation' for further details.
Long explanation:
I've been reading over the previous thread for this. I am faced with a
similar situation and would appreciate some syntax assistance. I am only
partially finished.
I have 2 tables to insert into:
candidate
resume
When a new entry is entered via an INSERT statement I have set up, an
auto-increment integer is assigned (Candidate_ID) in the 'candidate' table.
This insert is structured in PHP:
$query = INSERT INTO candidate (Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience, Location_Country)
values ('$lastname', '$firstname', '$middle', '', '$Employer', '', '',
'$visa_dt', '$MMDD_Birth', '$ssn', '$csg_comments', '$working',
'$Available', '', '$location', '', '$relocation', '$Tech_Ranking',
'$Comm_Ranking', '', '$cert_comments', '', '$Def_Rate', '$Def_Rate_Unit',
'', '', '$interview_availability', '$interview_contact', '$US_Experience',
'');
$result = mysql_query($query) or die(Error: . mysql_error());
--
Now, having entered that data, I need to insert different fields from my
form into 'resume'; syntax is probably the same, but where I'm stuck is how
to determine what auto-incremented number was assigned to the entry in
candidate, so I can then insert columns based on this.
resume is structured as so:
Field TypeNullDefault
Candidate_IDint(10) Yes NULL
Section_ID int(10) Yes NULL
Section_Value longtextYes NULL
Scenario is as follows:
New entry: James Brown, is assigned 10700 for Candidate_ID and is inserted
into table 'candidate'.
Following this insert, insert is required for 6 rows of data into table
'resume':
Candidate_ID must be 10700 / Section_ID = 1 / Section_Value = some form data
from textbox
Candidate_ID must be 10700 / Section_ID = 2 / Section_Value = some form data
from a different textbox
etc. until Section_ID 6.
How would I go about setting this up for resume, now that it's set up for
candidate?
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another insert/select for multiple tables... (or, determining previous auto-increment ID)
Since you should still have the same information that you used to create
your entry in the candidate table you can do something like:
SELECT @candidateID := Candidate_ID
FROM candidate
WHERE Last_Name = '$lastname'
AND First_Name = '$firstname'
AND Middle_Initial = '$middle'
INSERT INTO resume (Candidate_ID, Section_ID, Section_Value)
VALUES (@candidateID, 1, '$field1'),
(@candidateID, 2, '$field2'),
(@candidateID, 3, '$field3'),
(@candidateID, 4, '$field4'),
(@candidateID, 5, '$field5'),
(@candidateID, 6, '$field6');
If you use this format, you have to worry about your field data (field1
through field6) messing with your single quotes. This is especially true
as I think those are user-entered fields, right? I can't think of it off
the top of my head but I believe that PHP already has a function that will
safely escape text strings so that they are INSERTable into MySQL using
single quotes (sorry for the brain freeze).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eve Atley [EMAIL PROTECTED] wrote on 09/01/2004 03:02:21 PM:
The question:
Is there anything in MySQL that will allow me to determine, accurately,
the
last auto-incremented field from a particular database, so I can then
insert
based upon this into another table? What if 2 users input at the same
time?
Please see the 'long explanation' for further details.
Long explanation:
I've been reading over the previous thread for this. I am faced with a
similar situation and would appreciate some syntax assistance. I am only
partially finished.
I have 2 tables to insert into:
candidate
resume
When a new entry is entered via an INSERT statement I have set up, an
auto-increment integer is assigned (Candidate_ID) in the 'candidate'
table.
This insert is structured in PHP:
$query = INSERT INTO candidate (Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
values ('$lastname', '$firstname', '$middle', '', '$Employer', '', '',
'$visa_dt', '$MMDD_Birth', '$ssn', '$csg_comments', '$working',
'$Available', '', '$location', '', '$relocation', '$Tech_Ranking',
'$Comm_Ranking', '', '$cert_comments', '', '$Def_Rate',
'$Def_Rate_Unit',
'', '', '$interview_availability', '$interview_contact',
'$US_Experience',
'');
$result = mysql_query($query) or die(Error: . mysql_error());
--
Now, having entered that data, I need to insert different fields from my
form into 'resume'; syntax is probably the same, but where I'm stuck is
how
to determine what auto-incremented number was assigned to the entry in
candidate, so I can then insert columns based on this.
resume is structured as so:
Field Type Null Default
Candidate_ID int(10)Yes NULL
Section_ID int(10)Yes NULL
Section_Value longtextYes NULL
Scenario is as follows:
New entry: James Brown, is assigned 10700 for Candidate_ID and is
inserted
into table 'candidate'.
Following this insert, insert is required for 6 rows of data into table
'resume':
Candidate_ID must be 10700 / Section_ID = 1 / Section_Value = some form
data
from textbox
Candidate_ID must be 10700 / Section_ID = 2 / Section_Value = some form
data
from a different textbox
etc. until Section_ID 6.
How would I go about setting this up for resume, now that it's set up
for
candidate?
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another insert/select for multiple tables... (or, determining previous auto-increment ID)
Yes, LAST_INSERt_ID(). It's connection-specific, not db-specific. Because
it is connection-specific, your 2-users issue is avoided. See the manual
for the details http://dev.mysql.com/doc/mysql/en/Getting_unique_ID.html.
Michael
Eve Atley wrote:
The question:
Is there anything in MySQL that will allow me to determine, accurately, the
last auto-incremented field from a particular database, so I can then insert
based upon this into another table? What if 2 users input at the same time?
Please see the 'long explanation' for further details.
Long explanation:
I've been reading over the previous thread for this. I am faced with a
similar situation and would appreciate some syntax assistance. I am only
partially finished.
I have 2 tables to insert into:
candidate
resume
When a new entry is entered via an INSERT statement I have set up, an
auto-increment integer is assigned (Candidate_ID) in the 'candidate' table.
This insert is structured in PHP:
$query = INSERT INTO candidate (Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience, Location_Country)
values ('$lastname', '$firstname', '$middle', '', '$Employer', '', '',
'$visa_dt', '$MMDD_Birth', '$ssn', '$csg_comments', '$working',
'$Available', '', '$location', '', '$relocation', '$Tech_Ranking',
'$Comm_Ranking', '', '$cert_comments', '', '$Def_Rate', '$Def_Rate_Unit',
'', '', '$interview_availability', '$interview_contact', '$US_Experience',
'');
$result = mysql_query($query) or die(Error: . mysql_error());
--
Now, having entered that data, I need to insert different fields from my
form into 'resume'; syntax is probably the same, but where I'm stuck is how
to determine what auto-incremented number was assigned to the entry in
candidate, so I can then insert columns based on this.
resume is structured as so:
Field TypeNullDefault
Candidate_IDint(10) Yes NULL
Section_ID int(10) Yes NULL
Section_Value longtextYes NULL
Scenario is as follows:
New entry: James Brown, is assigned 10700 for Candidate_ID and is inserted
into table 'candidate'.
Following this insert, insert is required for 6 rows of data into table
'resume':
Candidate_ID must be 10700 / Section_ID = 1 / Section_Value = some form data
from textbox
Candidate_ID must be 10700 / Section_ID = 2 / Section_Value = some form data
from a different textbox
etc. until Section_ID 6.
How would I go about setting this up for resume, now that it's set up for
candidate?
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
