Tobi123 Tobi123 - 1 year ago 134
SQL Question

mysql insert only if not exists doesn't work

I read some threads about this topic and tried so much but my code does not work. If a dataset already exists in my table, i do not want to insert it twice.

My table "WORD" with some example data (id is auto increment).

id | customer_id | category_id | word | comment
1 3 5 life null
2 5 5 motor tbd
3 null 2 Day week

I ONLY want to check customer_id, category_id, word. If the new row has exactly same customer_id, category_id and word, I don't want to insert it. comment and id dosn't matter.

My Code:

insert into word (customer_id, category_id, word)
select * from (select null, 2, 'Day') as tmp
where not exists (select * from word where customer_id=null and category_id=2 and word='Day')
limit 1

THIS is inserted :(
He is inserting it, though I have a row (id =3) with null/2/Day. The new inserted one has comment null, the othe hast comment week, but this doesn't matter.

Where is my mistake?

Answer Source
select * from word where customer_id=null

Should be

select * from word where customer_id IS NULL

See this site in the manual:

A way to dodge the workaround in your statement would be to set an unique index on those columns, if your usecases allow the constraint.

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download