CODEWITHSUNDEEP
sqlpostgresqlduplicates
Fatin Az
Fatin Az

Reputation: 509

How to filter duplicates when using INSERT INTO

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.

Upvotes: 0

Views: 120

Answers (2)

Michaël Benjamin Saerens
Michaël Benjamin Saerens

Reputation: 875

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';

Upvotes: 0

user330315
user330315

Reputation:

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?

Upvotes: 2

Related Questions