First a bit of friendly advice:
When performing an INSERT...SELECT... it is better to explicitly list the
source columns (part of the SELECT statement). Doing so will ensure that
if you re-order some columns or if you add/drop columns to/from one of the
tables participating in the SELECT portion of your statement that your
INSERT will still function. It is also the only way to perform an INSERT
so that an auto_increment column works properly. I know you end up with
longer statements but the statements are less likely to break and they are
easier to debug.
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/20/2004 01:31:59 PM:
>
> I am attempting to copy data from 1 table into another, based on certain
> criteria. I have set up the following queries, but am unsure if they
> will function properly:
>
> #This pulls back all data for matching candidates with keyword from
> RESUME and CANDIDATE
> 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;
This query has a JOIN in it without an ON condition. Creating a JOIN in
this fashion relies on the optimizer to detect that you have a valid
table-to-table condition in your WHERE statement and apply it correctly.
If for some reason the optimizer misses this condition (c.Candidate_ID =
r.Candidate_ID) during the JOIN phase of the query (or if you neglected to
make one), you will produce a Cartesian product (every possible
combination of each row from both tables) of the tables you are joining
and will have to filter out all but the matching rows during the WHERE
phase of the query. I would re-write it this way:
SELECT *
FROM wow.resume r
INNER JOIN wow.candidate c
ON c.Candidate_ID = r.Candidate_ID
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE);
>
> #This must copy all corresponding fields from RESUME into RESUME_ERP
> *for each candidate ID previously found*
> INSERT INTO wow.resume_erp
> (Candidate_ID, Section_ID, Section_Value)
> SELECT SQL_CALC_FOUND_ROWS *
> FROM wow.resume r
> INNER JOIN wow.candidate c;
Here is an example of a INSERT...SELECT... that does not specify the
source columns. This is also an example of a Cartesian product. If you
have 12000 resumes and 10000 candidates then the resume_erp table will end
up with 12000000 (12 million) rows in it (12000 x 10000). This is probably
NOT what you wanted. I would probably rewrite it this way
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, r.Section_ID, r.Section_Value
FROM wow.resume r
INNER JOIN wow.candidate c
ON r.candidate_ID = c.candidate_ID;
I don't know all of your field names so I had to make up an ON clause.
Change the field names to match those actually in your system.
> #This must copy all corresponding fields from CANDIDATE into
> CANDIDATE_ERP *for each candidate ID previously found*
> 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 SQL_CALC_FOUND_ROWS *
> FROM wow.resume r
> INNER JOIN wow.candidate c;
This is another example of a Cartesian product. This also another example
of a "naked" SELECT clause.
Specify your source columns (do not use "SELECT SQL_CALC_FOUND_ROWS *")
and add an ON constraint to your INNER JOIN so that the appropriately
related rows are matched up.
>
> Am I on the right track?
Yes, you were close. May I suggest a little reading?
http://dev.mysql.com/doc/mysql/en/JOIN.html
http://dev.mysql.com/doc/mysql/en/INSERT.html
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html
>
> Please read further for the kind of data returned, if necessary.
>
> Thanks,
> Eve
>
<sample data snipped>
