CODEWITHSUNDEEP
mysql
Pawan
Pawan

Reputation: 32321

MySQL Insert only if not present or else do nothing

I am trying to insert a record into MySQL if the record doesn't exists or else do nothing

while inserting , I am checking if T1 ='one' AND T2 = 'two' AND T3 = 'three' AND vendor_brand_id = 7 doesn't exists then only insert or else do nothing

But this is producing a syntax error , could you please tell me whats wrong with this

This is my schema 

CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int(11) NOT NULL auto_increment,
  `T1` varchar(50) default NULL,
  `T2` varchar(50) default NULL,
  `T3` varchar(50) default NULL,
  `T4` varchar(50) default NULL,
  `T5` varchar(50) default NULL,
  `T6` varchar(50) default NULL,
  `T7` varchar(50) default NULL,
  `T8` varchar(50) default NULL,
  `T9` varchar(50) default NULL,
  `T10` varchar(50) default NULL,
  `vendor_brand_id` varchar(10) default NULL,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=384 DEFAULT CHARSET=latin1;

INSERT INTO `category` (`category_id`, `T1`, `T2`, `T3`, `T4`, `T5`, `T6`, `T7`, `T8`, `T9`, `T10`, `vendor_brand_id`, `created_at`) VALUES
    (377, 'one', 'two', 'three', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7', '2016-04-11 19:32:34'),
    (378, 'one', 'two', 'three', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7', '2016-04-11 19:32:50')

and this is my insert query 

Insert into category (T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,vendor_brand_id)
 values ('one', 'two', 'three',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7) 
 select category_id from category where T1 ='one' AND T2 = 'two'  AND T3 = 'three'  AND vendor_brand_id = 7  
  FROM dual 
  WHERE NOT EXISTS
    ( SELECT *
          FROM category
         where T1 ='one' AND T2 = 'two'  AND T3 = 'three'  AND vendor_brand_id = 7  
        ) ;

But this is producing a syntax error , could you please tell me whats wrong with this

http://sqlfiddle.com/#!9/f371e

Upvotes: 1

Views: 1282

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use a query like this:

INSERT INTO category (T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, vendor_brand_id)
SELECT T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, vendor_brand_id
FROM (
  SELECT CAST('one' AS CHAR CHARACTER SET latin1) AS T1, 
         CAST('two' AS CHAR CHARACTER SET latin1) AS T2, 
         CAST('three' AS CHAR CHARACTER SET latin1) AS T3,
         NULL AS T4, NULL AS T5, NULL AS T6, NULL AS T7, 
         NULL AS T8, NULL AS T9, NULL AS T10, 7 AS vendor_brand_id) AS t 
WHERE NOT EXISTS (SELECT *
                  FROM category AS c
                  WHERE c.T1 = t.T1 AND 
                        c.T2 = t.T2 AND 
                        c.T3 = t.T3 AND 
                        c.vendor_brand_id = t.vendor_brand_id)

Upvotes: 2

Related Questions