What is the definition of resume_erp (what is the result of: SHOW CREATE
TABLE resume_erp) ? What that error is telling you is that you are
attempting to add a record that matches a set of conditions that you said
could only exist once on the entire table (either your primary key or a
unique key). By looking at the definition of that table we can determine
where you should look for the duplication.
continued below...
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/22/2004 03:27:47 PM:
>
> I think we're on to something. I've got one table importing data
> correctly, resume to resume_erp. However, 'candidate' to 'candidate_erp'
> appears goofy. I get the following error:
>
> [mysql.loosefoot.com] ERROR 1062: Duplicate entry '1185' for key 1
>
> With the following queries, query 3 being the problematic one:
>
> # Connection: mysql.loosefoot.com
> # Host: mysql.loosefoot.com
> # Saved: 2004-10-22 14:51:15
> #
> CREATE TEMPORARY TABLE wow.tmpCandidates
> SELECT DISTINCT r.Candidate_ID
> FROM wow.resume r
> WHERE r.Section_ID = '1'
> AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE);
>
> 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.tmpCandidates tc
> INNER JOIN wow.resume r
> on r.Candidate_ID = tc.Candidate_ID
> INNER JOIN wow.candidate c
> ON c.Candidate_ID = tc.Candidate_ID;
The only reason you need to JOIN the table candidate to this query is to
make sure that you don't add any records to resume_erp unless they have a
corresponding record in candidate. If you are sure that all resumes still
have their corresponding candidate records then you can drop the second
INNER JOIN and just use the candidate_ID column from either resume or
tmpCandidates (your choice) in your SELECT clause.
>
> 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 c.Candidate_ID, c.Vendor_ID, c.Last_Name,
> c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
> c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
> c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
> c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
> c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
> c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
> c.Interview_Availability, c.Interview_Contact, c.US_Experience,
> c.Location_Country
> FROM wow.tmpCandidates tc
> INNER JOIN wow.resume r
> on r.candidate_ID = tc.Candidate_ID
> INNER JOIN wow.candidate c
> ON c.Candidate_ID = tc.Candidate_ID;
>
>
> But I think we're almost there....:)
Yes, we are.
>
> Thanks,
> Eve
You're welcome,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
