• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

MySQL conditional INSERT

Hello,

I am trying to do a conditional INSERT based on whether a product # already exists in my table.
Does anyone know how to do this?  I have tried several options, including the EXISTS and IF clauses, but it keeps throwing a syntax error.  Here's what I've tried so far:

INSERT INTO products ( productNum, price )
VALUES (12345, 100)
WHERE NOT EXISTS ( SELECT * FROM products WHERE productNum=12345 )

and...

IF NOT EXISTS (  SELECT * FROM products WHERE productNum=12345 )
THEN INSERT INTO products ( productNum, price )
           VALUES (12345, 100)
END IF


Any ideas?
0
chaseivey
Asked:
chaseivey
1 Solution
 
ishandoCommented:
try this

 INSERT INTO products ( productNum, price )
select 12345, 100
WHERE  NOT EXISTS ( SELECT 1 FROM products WHERE  productNum=12345 )

i don't have mySql handy to test but in Oracle I would do

INSERT INTO products ( productNum, price )
select 12345, 100 from dual
WHERE   NOT EXISTS ( SELECT 1 FROM products WHERE  productNum=12345 )
 
the "from dual" is optional in mySql

0
 
chaseiveyAuthor Commented:
Thanks man.  mySQL won't let you do a subquery on the same table, but the FROM DUAL worked like a charm.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now