How to delete record using below condition in sql

Question

I have the following tables and I need to remove the rows that satisfy a given condition.

Please help me.

Table: Product

productId productname datecreated
1         sample1     20/01/2012
2         sample1     20/01/2012
3         sample2     20/02/2012
4         sample2     20/02/2012
5         sample2     20/09/2012
6         sample1     20/08/2012

Table: Category

categoryid categoryname
1          cat1
2          cat2
3          cat3

Table: ProductCategory

postid  categoryid
1         1
4         2

Now I want to delete the rows which have the count > 1 group by productname and datecreated from first table and also the rows that are not contained in the productcategory table

i.e, I want to remove the

  ProductId 2 and ProductId 3

because it has the same ProductName with same DateCreated and also is not contained in the ProductCategory Table

thanks in advance

Answer 1

delete Product
from Product as P
where
    P.productId not in (select T.postid from ProductCategory as T) and
    exists
    (
        select T.*
        from Product as T
        where
            T.productId <> P.productId and
            T.productname = P.productname and
            T.datecreated = P.datecreated
    )

sql fiddle demo

Answer 2

You could first do this to remove all products that are not inside the ProductCategory table:

STEP 1

Delete from Product 
Where productId not IN(select productId from ProductCategory)

And then if you still want to remove duplicates from your Product table you could try this:

STEP 2

Select productname, datecreated, Count(*)
Into TheKeys
from Product 
Group by productname, datecreated
Having Count(*)>1

This TheKeys table would contain the duplicate values

STEP 3

Select DISTINCT Product.*
Into TheRows
From Product, TheKeys
Where Product.productname = TheKeys.productname
And Product.datecreated = TheKeys.datecreated

Table TheRows contains all the corresponding rows

STEP 4

Delete Product 
From Product, TheKeys
Where Product.productname = TheKeys.productname
and Product.datecreated = TheKeys.datecreated

this would delete all the duplicate rows

and lastly put the unique rows back into the table with this:

STEP 5

Insert into Product (productId, productname, datecreated)
Select * From TheRows

NOTE: If you dont mind having products that do not belong to any category You could leave out the first step and do this Right AFTER STEP 3

Delete From TheRows
Where productId not In(Select productId from ProductCategory)

and then continue with STEP 4