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
I need to delete only second row dublicate by partdetailsid asc
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
parttdetailid 1500 and 1700 must delete from table when partid repeated on partdetails and related to main parts parts table
So when delete second one partid repeating meaning
1500 for partid 1222
1700 for partid 1901
no delete will done to partid 5000 because not repeated