asked on

mysql insert into table where record does not already exist

I'm trying to insert a record into a table where the record to be inserted does not already exist but have not had success with the correct syntax.
I've tried this:

INSERT INTO teams (CJOcode, PowerStaffCode) VALUES ('6001', '41')
WHERE LENGTH((SELECT CJOcode FROM teams WHERE CJOcode='6001' AND PowerStaffCode='41')) = 0

Open in new window

and this:

INSERT INTO teams (CJOcode, PowerStaffCode) VALUES ('6001', '41')
WHERE CJOcode NOT IN
(SELECT CJOcode FROM teams WHERE CJOcode='6001' AND PowerStaffCode='41')

Open in new window

and this:

INSERT INTO teams AS t1 LEFT JOIN 
(SELECT CJOcode FROM teams t2 WHERE CJOcode='6001' AND PowerStaffCode='41') AS t2 
USING(CJOcode,PowerStaffCode) 
(t1.CJOcode, t1.PowerStaffCode) VALUES ('6001', '41')
WHERE t2.CJOcode IS NULL

Open in new window

Can anyone help correct my syntax?

Cornelia Yoder

You cannot use WHERE on an INSERT. Perhaps if you explain in words what you are trying to accomplish, we might find another way to do it.

ShanghaiD

ASKER

I have a table "teams" with pairs of unique values for each "CJOcode" and "PowerStaffCode", for example:
6001 41
6001 43
6002 43
....
I refresh this table by querying another table to extract distinct pairs of "CJOcode" and "PowerStaffCode" -- some of which already exist in "teams" but some of which do not.
I want to run an INSERT into "teams" for the various new pairs but only where the new pairs do not already exist in "teams".
Currently I can successfully do what I want by using CFML script combined with MySQL queries, as follows:

<!--- Insert new team person if form2 is submitted --->
<cfif IsDefined("FORM.form2submitted")>
 <cfquery name="checkteams">
 SELECT * FROM teams WHERE CJOcode='#FORM.cjo#' AND PowerStaffCode='#FORM.pickstaff#'
 </cfquery>
 <cfif #checkteams.RecordCount# EQ 0><!--- First check if not already in pwcjobteams--->
  <cfquery name="insertnewteamperson">
  INTO teams (CJOcode, PowerStaffCode) VALUES ('#FORM.cjo#', '#FORM.pickstaff#')
  </cfquery>
 </cfif>

Open in new window

but I am trying to find a more elegant solution using only MySQL queries (if possible).

Cornelia Yoder

Are the feilds CJOcode and PowerStaffCode primary keys in your table? If not, how do you ensure they are unique?

SOLUTION

Cornelia Yoder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ShanghaiD

ASKER

Thanks, yodercm for reminding me of the usefulness of primary keys -- although they are in fact set as such, I had not been relying on auto fail on duplicate key error.

Thanks, Sharath_123 for also finding the elegant MySQL single query solution which can also be used where primary keys are not able to be used.

ShanghaiD

ASKER

Sharath_123 - thanks also for bringing to my attention the new (for me) DUAL keywork in MySQL!