Insert multiple rows without duplicating them - MySQL

Question

I'm trying to execute this query nut it seems that it returns errors:

INSERT INTO `categories` (`name`,`path`) VALUES ('TEST 1' , 'test-1'),('TEST 2' , 'test-2'),('TEST 3' , 'test-3')
WHERE (`name`,`path`) NOT IN (SELECT `name`,`path` FROM `categories`);

Any help with this? Much appreciated.

You could add a unique key and use insert ignore into ... to insert the data — juergen d
– juergen d, Commented Jan 20, 2015 at 11:40

Gordon Linoff · Accepted Answer · 2015-01-20 11:43:41Z

1

The logic you are attempted is implemented using syntax like this:

INSERT INTO `categories` (`name`,`path`)
     SELECT name, path
     FROM (SELECT 'TEST 1' as name, 'test-1' as path UNION ALL
           SELECT 'TEST 2', 'test-2' UNION ALL
           SELECT 'TEST 3' , 'test-3'
          ) t
     WHERE NOT EXISTS (SELECT 1
                       FROM categories c
                       WHERE c.name = t.name and c.path = t.path
                      );

However, you should be doing the checking in the database with a unique index. So the better solution is to do the insert and just have:

create unique index idx_categories_name_path on categories(name, path)

With an insert like:

INSERT INTO `categories` (`name`,`path`)
     SELECT name, path
     FROM (SELECT 'TEST 1' as name, 'test-1' as path UNION ALL
           SELECT 'TEST 2', 'test-2' UNION ALL
           SELECT 'TEST 3' , 'test-3'
          ) t
     ON DUPLICATE KEY UPDATE name = VALUES(name);

answered Jan 20, 2015 at 11:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3350731 Over a year ago

The thing is the values can goes to 200 or more rows! is it ok if I proceed like u're answer ?

Gordon Linoff Over a year ago

@user3350731 . . . There is no problem at all with handling 200 values using either method.

Collectives™ on Stack Overflow

Insert multiple rows without duplicating them - MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related