CODEWITHSUNDEEP
mysqlsqldatabase
TIMEX
TIMEX

Reputation: 272374

How do I make a MySQL insert query that "inserts if not exists"?

INSERT INTO table('name') VALUES("abc") IF NOT EXISTS name='abc'

If abc doesn't exist in the name column, then insert it. How can I write that query?

Upvotes: 3

Views: 1049

Answers (6)

Misri
Misri

Reputation: 19

I have Some Code...hope it will help you..

mysql_query("INSERT INTO authors (author) VALUES ('$rec_fic_author') 
     WHERE NOT EXISTS (SELECT * FROM authors WHERE author='$rec_fic_author')") 
or die("cannot insert author");

here author is the name of table

authorID (pk)
author $rec_fic_author is _POST variable

Upvotes: 0

christiaanderidder
christiaanderidder

Reputation: 675

INSERT IGNORE INTO table(name) VALUES('abc')

This will ignore the value if it already exists. Like pjotr said, this will require name to be a unique index. Source

Upvotes: 4

Michał Powaga
Michał Powaga

Reputation: 23183

Try:

insert into table('name') 
select 'abc'
where not exists (select 1 from table where name='abc')

Upvotes: 3

voidengine
voidengine

Reputation: 2579

You may either use REPLACE (syntax, or, equivalent INSERT ON DUPLICATE KEY UPDATE). This is more appropriate if there's more columns and you want to update the others for the given key.

Or the IGNORE modifier (INSERT syntax) along with a unique index for the 'name' column. In that case, the insert will be ignored if it violates the unique index, but won't throw an error. That's more appropriate if you don't want to change any values and just keep the record if it already exists.

Upvotes: 2

Savino Sguera
Savino Sguera

Reputation: 3572

I would enforce the column as UNIQUE and catch the exception on the code side, if you have a unicity constraint on that field. Otherwise I tend to agree with other answers.

Upvotes: 0

aF.
aF.

Reputation: 66757

One way to do it is testing it with an IF:

IF (select count(*) from table where name = 'abc') = 0
THEN
   INSERT INTO table('name') VALUES("abc")

Upvotes: 1

Related Questions