How to delete Second PartDetailsId for Reapeated parts Based on CompanyId and PartId?

Transact-SQL
,
1

I work on SQL server 2012 . I face issue I can't delete Second PartDetailsId from part details

based on part ID and company id that related to main table parts .

if Part ID repeated on partdetails then I get Second PartDetailsId by companyid and partid

as Part ID 1222 and 1901 and delete Second partsDetailsId from partdetails Table group by company id and partid.

if Part ID not Repeated row on part details Table then I will not delete it as
Part ID 5000

 create table #parts
 (
 PartId int,
 PartNumber nvarchar(50)
 )
 insert into #parts(PartId,PartNumber)
 values
 (1222,'x54310'),
 (1255,'m90110'),
 (1901,'f43398'),
 (5000,'gyzm30')
    
 create table #partsDetails
 (
 partsDetailsId  int,
 PartId  int,
 CompanyId int
 )
 insert into #partsDetails(partsDetailsId,partid,CompanyId)
 values
 (1225,1222,2020),
 (1500,1222,2020),
 (1600,1222,2020),
 (1650,1901,2030),
 (1700,1901,2030),
 (1750,5000,2707)

so deleted PartDetailsId will be :

 partsDetailsId    PartId    CompanyId
 1500                   1222    2020
 1700                    1901    2030
2

what have you tried?

3

what I try like that
DELETE FROM #partsDetails
WHERE partsDetailsId IN
(
SELECT partsDetailsId FROM
(

)p

WHERE Number = 2
)

4

If you had to think in terms of basic English or whatever is the language of your heart what does this mean?

WHERE partsDetailsId IN

You have been asking on this site for almost a year now so its time for you to provide and attempt to figure this out.

5

I solved thanks
my solution as below :slight_smile:

    DELETE FROM #partsDetails
    WHERE partsDetailsId IN 
    (
    	SELECT partsDetailsId FROM
    	(
    		SELECT ROW_NUMBER() OVER (PARTITION BY partid ORDER BY (Select 0)) Number, 
    		partsDetailsId,partid,CompanyId 
    		FROM #partsDetails
    	)p
    WHERE Number = 2
    )
1 Like