Mysql Insert record if not Exists

Question

I have the following query

INSERT INTO `title_servicemetadatafielddefinition` (`service_id`, `field`, `behavior`, `alt_label`, `localizable`, `custom_type`, `required`, `max_length`, `help_text`) 
VALUES (319, 'custom10', 'overridable', 'Rental Period', False, 'short_text', False, NULL, '24 hour;48 hour;72 hour;1 week;30 day;3 month;6 month;1 year') 
WHERE NOT EXISTS ( SELECT * FROM `title_servicemetadatafielddefinition` WHERE `service_id` = 319 and `field` = 'custom10' ) LIMIT 1;

Error

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 'WHERE NOT EXISTS ( SELECT * FROM title_servicemetadatafielddefinition WHERE `s' at line 1

and was failing when trying to execute, can't able to figure out what syntax was wrong

possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — Panagiotis Kanavos, Sep 01 '15 at 11:50

score 1 · Answer 1 · answered Sep 01 '15 at 10:09

Below query will insert if row does not exist and ignore if already exist but make sure that service_id here should be either primary key or unique key.

INSERT IGNORE INTO `title_servicemetadatafielddefinition` (`service_id`, `field`, `behavior`, `alt_label`, `localizable`, `custom_type`, `required`, `max_length`, `help_text`) 
VALUES (319, 'custom10', 'overridable', 'Rental Period', False, 'short_text', False, NULL, '24 hour;48 hour;72 hour;1 week;30 day;3 month;6 month;1 year')

Bohemian · Answer 2 · 2015-09-01T11:37:54.337

0

Try this:

INSERT INTO `title_servicemetadatafielddefinition` (`service_id`, `field`, `behavior`, `alt_label`, `localizable`, `custom_type`, `required`, `max_length`, `help_text`) 
SELECT 319, 'custom10', 'overridable', 'Rental Period', False, 'short_text', False, NULL, '24 hour;48 hour;72 hour;1 week;30 day;3 month;6 month;1 year'
FROM (SELECT 1) x
LEFT JOIN `title_servicemetadatafielddefinition` t
  ON t.`service_id` = 319 and t.`field` = 'custom10'
WHERE t.service_id IS NULL

edited Sep 01 '15 at 11:37

answered Sep 01 '15 at 10:01

Bohemian

389,931
88
552
692

Error was `Duplicate column name 'FALSE'` – Shiva Krishna Bavandla Sep 01 '15 at 10:04
@shivakrishna i think i fixed it - try that – Bohemian Sep 01 '15 at 11:40

score 0 · Answer 3 · edited May 23 '17 at 12:22

You Wrote 'Limit' in INSERT STATEMENT. I think, Limit should come in SELECT Statement.

INSERT INTO `title_servicemetadatafielddefinition` (`service_id`, `field`, `behavior`, `alt_label`, `localizable`, `custom_type`, `required`, `max_length`, `help_text`) 
VALUES (319, 'custom10', 'overridable', 'Rental Period', False, 'short_text', False, NULL, '24 hour;48 hour;72 hour;1 week;30 day;3 month;6 month;1 year') 
WHERE NOT EXISTS ( SELECT * FROM `title_servicemetadatafielddefinition` WHERE `service_id` = 319 and `field` = 'custom10' LIMIT 1);

Use "LIMIT" in a MySQL "INSERT"?

`INSERT` doesn't have a WHERE clause – Panagiotis Kanavos Sep 01 '15 at 11:51 — Panagiotis Kanavos, Sep 01 '15 at 11:51

Mysql Insert record if not Exists

3 Answers3