23

My database has a table called fruit:

Table fruit

+-------------+
| id | name   |
+ ----------- +
| 1  | apple  |
| 2  | orange |
| 3  | banana |
| 4  | grape  |
+-------------+

id is the a primary key. I want to add entries to the table, but only if they don't exist already.

The query

IF NOT EXISTS (SELECT name FROM fruit WHERE name = 'mango')
INSERT INTO fruit (name) 
VALUES ('mango')

Error

I use a SQL GUI app called Sequel Pro, and this query errors with the following:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT name FROM fruit WHERE name = 'mango') INSERT INTO frui' at line 1

Perhaps

Something fishy may be going on. The query may be stopping at INSERT INTO frui. Problem with the app? Or is my query wrong?

izolate
  • 1,520
  • 4
  • 22
  • 33
  • 2
    Your approach is wrong. Add a unique index on `name`, then use `INSERT IGNORE` and check the number of inserted rows. –  Jan 04 '14 at 23:49
  • @MikeW Can you answer with an example of how to do that please? – izolate Jan 04 '14 at 23:52
  • 1
    @izolate Looks like someone has beaten me to it. –  Jan 05 '14 at 00:05

1 Answers1

53

You'd have to use

ALTER TABLE fruit ADD UNIQUE (name)

and then use

INSERT IGNORE INTO fruit (name) VALUES ('mango')
ShiningLight
  • 1,017
  • 10
  • 13