I am so happy to get you working. Hopefully we helped some other along the way ,too :-)
It's a good idea when working with data that you should always clean up after yourself, regardless of what language you are using. Not only does it free up resources faster it helps to make sure that you don't end up with a contention issue like this. I take the blame for this one! I gave you the CREATE TEMPORARY... without the corresponding DROP TEMPORARY TABLE. http://dev.mysql.com/doc/mysql/en/DROP_TABLE.html Just put: DROP TEMPORARY TABLE tmpCandidates at the end of each pass and you will get rid of the temp table. No more error message. Temp tables are connection specific so you don't have to worry about more than one user/process sharing the same temp table, unless they share the same database connection (connection pooling is one example) Come back to the list if you need any more help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Eve Atley" <[EMAIL PROTECTED]> wrote on 10/25/2004 12:48:43 PM: > > I can't tell you how *glad* I am to get this running! Big hugs to you, > Shawn! > > It's running beautifully. My only question is, after I run the queries, > I notice it won't let me create the temporary table again (saying > 'tmpCandidates' already exists). Do I just need to then log out of my > client (MySQL Control Center) and back in to get rid of that temp table? > As I'll need to change what it searches for (ie baan, peoplesoft, etc.). > Or is there a query I can put in at the end of the queries to destroy > the temporary table once through with it? > > Final query setup posted below. > > Thanks, > Eve > > > 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 ('+peoplesoft' IN BOOLEAN > MODE); > > INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID, > Section_Value) > SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID, > r.Section_Value > FROM wow.tmpCandidates tc > INNER JOIN wow.resume r > on r.Candidate_ID = tc.Candidate_ID; > > INSERT IGNORE 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.candidate c > ON c.Candidate_ID = tc.Candidate_ID; >
