Multiple Insert Statement?
My query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Is it possible to do a multiple insert statement like so?
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, etc.)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Or do I have to break out the INSERT statements seperately?
Thanks!
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Insert Statement?
I know that this is off topic and such... but can you explain the
Match / Against that you used in your query? i have never seen syntax
like that in SQL
On Tue, 28 Sep 2004 14:16:56 -0400, Eve Atley [EMAIL PROTECTED] wrote:
My query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Is it possible to do a multiple insert statement like so?
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, etc.)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Or do I have to break out the INSERT statements seperately?
Thanks!
- Eve
--
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 Insert Statement?
At 14:16 -0400 9/28/04, Eve Atley wrote:
My query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Is it possible to do a multiple insert statement like so?
No.
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, etc.)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Or do I have to break out the INSERT statements seperately?
Yes.
--
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: Multiple Insert Statement?
That's the syntax used to do a full-text search in MySQL. Here's some
light reading:
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
GH [EMAIL PROTECTED] wrote on 09/28/2004 03:14:21 PM:
I know that this is off topic and such... but can you explain the
Match / Against that you used in your query? i have never seen syntax
like that in SQL
On Tue, 28 Sep 2004 14:16:56 -0400, Eve Atley [EMAIL PROTECTED]
wrote:
My query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE
r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Is it possible to do a multiple insert statement like so?
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, etc.)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Or do I have to break out the INSERT statements seperately?
Thanks!
- Eve
--
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 Insert Statement?
Then I need help getting on the right track here. What I really want to do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, 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)
SELECT *
FROM wow.candidate c
WHERE c.Candidate_ID = '13103';
Yet pulled from the resultset in this query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Perhaps the above isn't set up correctly, as when I attempt these queries:
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.candidate;
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, 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)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume;
...it returns an error of 1136: Column count doesn't match value count at
row 1. The query INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID = '13103';
is impractical when my results are over 400.
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple Insert Statement?
At 02:56 PM 9/28/2004, you wrote:
Then I need help getting on the right track here. What I really want to do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
You need to match up the columns in the Insert to the Select statement
(they both have to have the same number of columns and same column types
are preferred). So explicitly specify the columns in the Select statement as:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT
Candidate_ID, Section_ID, Section_Value FROM wow.resume r WHERE
r.Candidate_ID = '13103';
Using * on your Select statements to fill an Insert is dangerous because
the table structure could change in the future.
Mike
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, 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)
SELECT *
FROM wow.candidate c
WHERE c.Candidate_ID = '13103';
Yet pulled from the resultset in this query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Perhaps the above isn't set up correctly, as when I attempt these queries:
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.candidate;
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, 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)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume;
...it returns an error of 1136: Column count doesn't match value count at
row 1. The query INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID = '13103';
is impractical when my results are over 400.
Thanks,
Eve
--
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 Insert Statement?
- Original Message -
From: Eve Atley [EMAIL PROTECTED]
To: Paul DuBois [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 3:56 PM
Subject: RE: Multiple Insert Statement?
Then I need help getting on the right track here. What I really want to do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, 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)
SELECT *
FROM wow.candidate c
WHERE c.Candidate_ID = '13103';
Yet pulled from the resultset in this query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Perhaps the above isn't set up correctly, as when I attempt these queries:
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.candidate;
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, 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)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume;
...it returns an error of 1136: Column count doesn't match value count at
row 1. The query INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID = '13103';
is impractical when my results are over 400.
The following simple script illustrates that multiple rows can be copied
from one table into another by putting a Select from the source table within
the Insert for the target table.
#use tmp;
#Drop/Create source table
drop table if exists source;
create table if not exists source
(idno smallint not null,
surname char(10) not null,
primary key(id));
#Populate source table
insert into source (idno, surname) values
(1, 'Adams'),
(2, 'Bailey'),
(3, 'Collins');
#Display populated source table
select * from source;
#Drop/Create target table
drop table if exists target;
create table if not exists target
(id smallint not null,
name char(10) not null,
primary key(id));
#Populate target table
insert into target (id, name)
select * from source;
#Display populated target table
select * from target;
The Insert/Select (second last statement in the script) will work as long as
the column list, which is '(id, name)' in this case, has the same number of
columns as is returned by the Select clause. In this case, the source table
has two columns so 'select *' returns two columns so we have satisfied this
requirement.
Also, the two columns identified in the column list must correspond in
datatype and size to the columns listed in the select. In this case, 'select
* from source' translates into 'select idno, surname from source'; idno is a
smallint as is the corresponding column in the target table, id; surname is
a char(10) as is the corresponding column in the target table, name.
Therefore, the Insert/Select works.
The Insert/Select could also have been written 'insert into target(id, name)
select idno, surname from source' and still worked.
However, this would not have worked:
insert into target(id, name) select surname, idno from source;
because the column names don't correspond in datatype and length: id does
not correspond to surname and name does not correspond to idno.
I hope this clarifies the use of Insert/Select for you.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Insert Statement?
Sorry, there were a few typos in my reply. I have amended the reply at the
bottom
Rhino
- Original Message -
From: Rhino [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; Paul DuBois [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 5:41 PM
Subject: Re: Multiple Insert Statement?
- Original Message -
From: Eve Atley [EMAIL PROTECTED]
To: Paul DuBois [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 3:56 PM
Subject: RE: Multiple Insert Statement?
Then I need help getting on the right track here. What I really want to
do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, 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)
SELECT *
FROM wow.candidate c
WHERE c.Candidate_ID = '13103';
Yet pulled from the resultset in this query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
=
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Perhaps the above isn't set up correctly, as when I attempt these
queries:
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.candidate;
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, 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)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume;
...it returns an error of 1136: Column count doesn't match value count
at
row 1. The query INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID =
'13103';
is impractical when my results are over 400.
The following simple script illustrates that multiple rows can be copied
from one table into another by putting a Select from the source table
within
the Insert for the target table.
#use tmp;
#Drop/Create source table
drop table if exists source;
create table if not exists source
(idno smallint not null,
surname char(10) not null,
primary key(id));
#Populate source table
insert into source (idno, surname) values
(1, 'Adams'),
(2, 'Bailey'),
(3, 'Collins');
#Display populated source table
select * from source;
#Drop/Create target table
drop table if exists target;
create table if not exists target
(id smallint not null,
name char(10) not null,
primary key(id));
#Populate target table
insert into target (id, name)
select * from source;
#Display populated target table
select * from target;
The Insert/Select (second last statement in the script) will work as long
as
the column list, which is '(id, name)' in this case, has the same number
of
columns as is returned by the Select clause. In this case, the source
table
has two columns so 'select *' returns two columns so we have satisfied
this
requirement.
Also, the two columns identified in the column list must correspond in
datatype and size to the columns listed in the select. In this case,
'select
* from source' translates into 'select idno, surname from source'; idno is
a
smallint as is the corresponding column in the target table, id; surname
is
a char(10) as is the corresponding column in the target table, name.
Therefore, the Insert/Select works.
The Insert/Select could also have been written 'insert into target(id,
name)
select idno, surname from source' and still worked.
However, this would not have worked:
insert into target(id, name) select surname, idno from source;
because the column names don't correspond in datatype and length: id does
not correspond to surname and name does not correspond to idno.
I hope this clarifies the use of Insert/Select for you.
===
AMENDED REPLY
===
Most of what I said above is correct but the script had a mistake. (I
started editing the script on the fly to improve it but wasn't able to test
the amended version due to a temporary glitch on our server. I sent the note
anyway, assuming it was correct, and only discovered
