How to filter duplicates when using INSERT INTO

Question

I want to insert a new data but I want to put a condition inside query, if there is data exist, it will not insert, if not it can be inserted.

INSERT INTO user_permission_index (guid, code, name,description, perm_type, time_create)   
VALUES 
(uuid_generate_v1(),'perm_trading_cashsale_by_branch','label_CanissueCashsaleByBranch','','acc_branch_index','0001-01-01') 
WHERE NOT EXISTS (SELECT * 
                  FROM user_permission_index 
                  WHERE code = 'perm_trading_cashsale_by_branch' 
                    AND name = 'label_CanissueCashsaleByBranch');

I got syntax error at WHERE NOT. Can you guys help me. Thanks.

If it is oracle, i would suggest a merge. – Srini V Sep 25 '13 at 09:08 — Srini V, Sep 25 '13 at 09:08

score 2 · Accepted Answer · edited May 23 '17 at 11:56

You need to use a SELECT instead of VALUES

INSERT INTO user_permission_index (guid, code, name,description, perm_type, time_create)   
select uuid_generate_v1(),
       'perm_trading_cashsale_by_branch',
       'label_CanissueCashsaleByBranch',
       '',
       'acc_branch_index',
       '0001-01-01' 
WHERE NOT EXISTS (SELECT * 
                  FROM user_permission_index 
                  WHERE code = 'perm_trading_cashsale_by_branch' 
                    AND name = 'label_CanissueCashsaleByBranch');

(I'm assuming you are PostgreSQL because of the uuid_generate_v1() function)

If you are after an "update or insert" functionality you might want to look at this: Insert, on duplicate update in PostgreSQL?

score 0 · Answer 2 · answered Sep 25 '13 at 09:07

You need to link your inner select to your outer insert statement

INSERT INTO user_permission_index upi (guid, code, name,description, perm_type, time_create) 
SELECT (uuid_generate_v1(),'perm_trading_cashsale_by_branch','label_CanissueCashsaleByBranch','','acc_branch_index','0001-01-01') 
WHERE NOT EXISTS (
SELECT * 
FROM user_permission_index 
WHERE code = 'perm_trading_cashsale_by_branch' 
  AND name = 'label_CanissueCashsaleByBranch'
      AND id = upi.id
);

But I think it would be even better to say

INSERT INTO user_permission_index (guid, code, name,description, perm_type, time_create) 
SELECT (uuid_generate_v1(),'perm_trading_cashsale_by_branch','label_CanissueCashsaleByBranch','','acc_branch_index','0001-01-01') 
WHERE nvl(code, '') <> 'perm_trading_cashsale_by_branch'
   OR nvl(name, '') <> 'label_CanissueCashsaleByBranch';

How to filter duplicates when using INSERT INTO

2 Answers2