zeKPnqRa

1START TRANSACTION;
2
3CREATE TABLE IF NOT EXISTS `privileges` (
4    `id` TINYINT NOT NULL AUTO_INCREMENT,
5    `label` VARCHAR(25) UNIQUE,
6    PRIMARY KEY (`id`)
7) ENGINE=InnoDB DEFAULT CHARSET=utf8;
8
9INSERT INTO `privileges` (`label`)
10SELECT `label` FROM (
11SELECT NULL AS `label`
12UNION VALUES
13    ('item1'),
14    ('item2')
15) X
16WHERE `label` IS NOT NULL
17AND `label` NOT IN (SELECT `label` FROM `privileges`)
18
19COMMIT;
20	
21with ITEMS(label) as
22(VALUES 
23 ('item1')
24,('item2'))
25select i.label 
26from ITEMS i
27where not exists (select 1 from privileges p where p.label = i.label)
28	
29INSERT INTO privileges (label)
30SELECT label
31FROM (
32 SELECT 'item1' as label UNION ALL 
33 SELECT 'item2'
34) i
35WHERE NOT EXISTS (SELECT 1 FROM privileges p WHERE p.label = i.label);
36	
37CREATE TEMPORARY TABLE tmp_items (label VARCHAR(25) NOT NULL PRIMARY KEY);
38
39INSERT INTO tmp_items (label) VALUES 
40 ('item1')
41,('item2');
42
43INSERT INTO privileges (label)
44SELECT label
45FROM tmp_items i
46WHERE label NOT IN (SELECT DISTINCT label FROM privileges);
47	
48INSERT INTO `privileges` (`label`)
49VALUES
50    ('item1'),
51    ('item2')
52)
53ON DUPLICATE KEY UPDATE `label` = `label`