Finding Duplicate data existing in Database

[meenakshi]

I have 2 fields called Product_ID and Partner Product_ID where lets say product ID is P-001 and Partner product ID is P-P-0001 which is already existing in database with this combination and again if i try to insert same combination , it should throw error as duplicate. And if the combination not repeated then it should insert data to database.
Partner Product ID- Product ID
P-P-000001-P-0000001
P-P-000002-P-0000002
P-P-000003-P-0000001
Product ID can be repeated any times, but partner product ID should not be repeated and both of these fields combination should be unique.
Please help with above scenario.
@alchemiz @Dheeraj

[meenakshi]

Please help with this @darshthakkar , @AleksandarAngelevski

Thanks in Advance

[bgiles]

A good starting point is finding a SQL implementation - it will give you an idea of what’s possible on your database.

For instance in PostgreSQL you would want to use INSERT … ON CONFLICT (product_id) … however the only options seem to be DO NOTHING or DO UPDATE SET …. It’s not possible to raise an error (which would kill the command) or run an arbitrary stored procedure.

Instead you would need to use something like this

WITH both as (SELECT id FROM TABLE table1 INTERSECT SELECT id FROM TABLE2)
INSERT id, value INTO table VALUES (SELECT id, value FROM table2 WHERE id NOT IN both.id) AS X;
INSERT * INTO duplicates FROM (SELECT * FROM table2 WHERE id IN both.id) AS X;

(that’s off the top of my head - there may be small syntax errors.)

This is why snaps don’t always implement “obvious” features like writing duplicates to an error view. We want to keep the database snaps as consistent as possible and sometimes a feature that is easy to implement on one database would require a lot of work under the covers on other databases.

[alchemiz]

Here’s a simple script that will return all duplicate product_id and partner product_id combination

select	a.part_prod_id, a.num_row 
from	(
			select	concat([parter product id], [product id]) as part_prod_id, count(concat([parter product id], [product id])) as num_row
			from	yourtable (nolock) 
			group	by concat([parter product id], [product id])
		) a
where	a.num_row > 1

[meenakshi]

Thankyou @alchemiz and @bgiles for responding