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

  • Question

  • User696604810 posted

    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


    Friday, December 18, 2020 11:58 PM

All replies

  • User-1330468790 posted

    Hi ahmedbarbary,

     

    You could use ROW_NUMBER() function to grant an extra idenitifier which will be treated as a number of order.

    Therefore, you could delete all rows which ranks second in the table.

     

    More details, you could refer to below codes.

    SQL query:

    DROP TABLE IF EXISTS #parts
    DROP TABLE IF EXISTS #partsDetails
    DROP TABLE IF EXISTS #temTable
    
    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)
    
    
    -- filter out the second PartDetailsId with the same partid and CompanyId
    SELECT [partsDetailsId]
    INTO #temTable
    FROM 
        (SELECT partsDetailsId,  ROW_NUMBER() OVER (PARTITION BY partid,CompanyId ORDER BY partsDetailsId) rowNum FROM #partsDetails ) a
        WHERE  rowNum = 2
    
    -- display the result set 
    SELECT * FROM #temTable
    
    
    -- delete the second PartDetailsId using partsDetailsId
    DELETE FROM #partsDetails WHERE partsDetailsId IN (SELECT [partsDetailsId] FROM  #temTable)
    
    -- display the result set
    SELECT * FROM #partsDetails

    Result:

    Reference:

    ROW_NUMBER(): https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15

    A similar usage of ROW_NUMBER() function: https://stackoverflow.com/questions/48406742/delete-duplicate-rows-in-sql

      

    Hope helps.

    Best regards,

    Sean

    Monday, December 21, 2020 6:47 AM
  • User452040443 posted

    Try:

    delete p
    from #partsDetails as p
    where
      exists
        (select 1 from #partsDetails as s
         where 
           s.partsDetailsId < p.partsDetailsId and
           s.partid = p.partid and
           s.CompanyId = p.CompanyId)
    

    Hope this help

    Monday, December 21, 2020 12:33 PM