CODEWITHSUNDEEP
mysqlinsert-into
Shiva Krishna Bavandla
Shiva Krishna Bavandla

Reputation: 26648

Mysql Insert record if not Exists

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

Upvotes: 0

Views: 192

Answers (3)

Bohemian
Bohemian

Reputation: 424983

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

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6844

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')

Upvotes: 1

Nana Partykar
Nana Partykar

Reputation: 10548

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"?

Upvotes: 0

Related Questions