Post reply

How to delete Max PartDetailsId for Reapeated parts Based on CompanyId and PartI

  • December 19, 2020 at 12:02 am

    #3822251

     

    I work on SQL server 2012 . I face issue I can't delete max 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 max PartDetailsId by companyid and partid

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

    if Part ID not Repeated row 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
     1600                    1222    2020
     1700                    1901    2030

  • December 19, 2020 at 1:13 am

    #3822267

    Find the records to delete in an aggregated  subquery and then join it back to the table:

     SELECT D.*
     --DELETE D
     FROM #partsDetails D
    INNER JOIN (
    SELECT MAX(partsDetailsId) MaxPartDetailsID,partid,CompanyId
    FROM #partsDetails 
    GROUP BY partid,CompanyId
    HAVING COUNT(*) > 1 
    ) M ON M.MaxPartDetailsID = D.partsDetailsId AND M.partid = D.partid AND M.CompanyId = D.CompanyId

    Once you happy with the set of records returned by SELECT comment SELECT D.* and uncomment DELETE D. Displayed rows will be deleted.

    If you run the SELECT query again you'll find another candidate for deletion. Not sure if you want that one to be deleted as well.

    _____________
    Code for TallyGenerator

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply