Learn how to a build a cloud-first strategyRegister Now


mysql insert into table where record does not already exist

Posted on 2011-05-02
Medium Priority
Last Modified: 2012-05-11
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')
(SELECT CJOcode FROM teams WHERE CJOcode='6001' AND PowerStaffCode='41')

Open in new window

and this:
(SELECT CJOcode FROM teams t2 WHERE CJOcode='6001' AND PowerStaffCode='41') AS t2 
(t1.CJOcode, t1.PowerStaffCode) VALUES ('6001', '41')

Open in new window

Can anyone help correct my syntax?
Question by:ShanghaiD
  • 3
  • 3
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 35510026
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.


Author Comment

ID: 35510049
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#'
 <cfif #checkteams.RecordCount# EQ 0><!--- First check if not already in pwcjobteams--->
  <cfquery name="insertnewteamperson">
  INTO teams (CJOcode, PowerStaffCode) VALUES ('#FORM.cjo#', '#FORM.pickstaff#')

Open in new window

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

Expert Comment

by:Cornelia Yoder
ID: 35510057
Are the feilds CJOcode and PowerStaffCode primary keys in your table?   If not, how do you ensure they are unique?
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

LVL 27

Assisted Solution

by:Cornelia Yoder
Cornelia Yoder earned 1000 total points
ID: 35510067
IF those fields are primary keys, you can do an INSERT using them and if they already exist, the INSERT will simply fail on a duplicate key error.  This won't hurt a thing, and you can suppress any error message if needed.

IF those fields are NOT primary keys, and you ensure uniqueness by checking before adding them, then you need two queries, one to determine if the pair is already in the table, and a second one to INSERT when it is not.

Tell me which case you have and I'll give you the queries for it.
LVL 41

Accepted Solution

Sharath earned 1000 total points
ID: 35510081
try like this.
SELECT '6001','41' 
  FROM dual 
                     FROM teams 
                    WHERE CJOcode = '6001' 
                          AND PowerStaffCode = '41');  

Open in new window


Author Closing Comment

ID: 35510105
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.

Author Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question