How to use where not exist in mysql?

Question

I want to insert a data if not exists in db at mysql .

I used following query:

INSERT INTO market 
VALUES('DZ','PM','23') 
WHERE NOT EXISTS (Select name from market where name ='DZ')

but it did not work.

Could you tell me a way how to fix it?

Answer 1

The generic solution for this is:

INSERT INTO market 
SELECT * FROM (SELECT 'DZ','PM','23') x
WHERE NOT EXISTS (select name from market where name = 'DZ')

The reason you need a subquery is that you can't have a WHERE without a FROM.

---

In Oracle, you can do it without a subquery by using DUAL:

INSERT INTO market 
SELECT 'DZ','PM','23'
FROM DUAL  -- Simpler in Oracle
WHERE NOT EXISTS (select name from market where name = 'DZ')