Insert record if not exists in mysql

Question

May be this question was asked many times, but not working in my case.

Table Schema

CREATE TABLE `tasks_taskstatus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `video_status` tinyint(1) NOT NULL,
  `creative_status` tinyint(1) NOT NULL DEFAULT '0',
  `missing_asset` tinyint(1) NOT NULL,
  `is_finished` tinyint(1) NOT NULL,
  `share_with_am` tinyint(1) NOT NULL,
  `ordering` smallint(6) NOT NULL,
  `ready_to_begin` tinyint(1) NOT NULL,
  `in_progress` tinyint(1) NOT NULL,
  `is_conformed` tinyint(1) NOT NULL,
  `order_item_equivalent` varchar(60) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=latin1;

I need to insert a record in mysql database if it was not already exists, below is the query i was trying to use.

    INSERT IGNORE INTO `tasks_taskstatus` 
SET `name`='Saveout - In Progress', 
`video_status`=1, 
`creative_status`=0, 
`missing_asset`=0, 
`is_finished`=0, 
`share_with_am`=0, 
`ordering`=4, 
`ready_to_begin`=0, 
`in_progress`=1, 
`is_conformed`=0, 
`order_item_equivalent`='';

But everytime i run this query a new record was being created, so what's wrong in the above statement and how can i modify the above query in order to insert the data/record only if not exists?

Possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — Brett DeWoody, Jan 29 '16 at 08:04
@BrettDeWoody Tried all the ways mentioned in your comment link, but was not working — Shiva Krishna Bavandla, Jan 29 '16 at 08:05
If you look through your records, how are they different on each new insert? — Brett DeWoody, Jan 29 '16 at 08:07
@BrettDeWoody OK they should be different on name and video_status fields — Shiva Krishna Bavandla, Jan 29 '16 at 08:08

Bart Friederichs · Answer 1 · 2016-01-29T08:19:42.410

0

You don't INSERT an id (which is your primary key), so the AUTO_INCREMENT kicks in and generates a new id and inserts a new record.

Ask yourself: how would MySQL know which record to update? If you know that (an example answer could be "by the name"), then you can change your keys and this should work.

If the combination of name and video_status has to be unique, create an extra key:

ALTER TABLE `tasks_taskstatus` ADD UNIQUE(`name`, `video_status`);

edited Jan 29 '16 at 08:19

answered Jan 29 '16 at 08:09

Bart Friederichs

30,888
13
85
169

ok the records should be differentiated based on name and video_status fields – Shiva Krishna Bavandla Jan 29 '16 at 08:14
how about this ? `INSERT INTO `tasks_taskstatus` (`name`, `video_status`, `creative_status`, `missing_asset`, `is_finished`, `share_with_am`, `ordering`, `ready_to_begin`, `in_progress`, `is_conformed`, `order_item_equivalent`) VALUES ('Saveout - In Progress', 1, 0, 0, 0, 0, 4, 0, 1, 0,'') WHERE NOT EXISTS (SELECT * from `tasks_taskstatus` WHERE `name`='Saveout - In Progress' AND `video_status`=1);` – Shiva Krishna Bavandla Jan 29 '16 at 08:14
@shivakrishna that would insert a new record. `INSERT IGNORE` and `REPLACE` work, if there would be a key violation otherwise. Change your table schema to add a `UNIQUE` key to `name` and `video_status`. – Bart Friederichs Jan 29 '16 at 08:18

Insert record if not exists in mysql

1 Answers1