locked
Query for delete cloned records RRS feed

  • Question

  • User110780343 posted

    I need a query for do this: when I've records with same IDDipendente and IDAttivita and IDMansione I want to keep only 1 record (not important if first or second or last... I need to keep one and delete others)

    so if I've record with

    IDDipendente | IDMansione | IDAttivita| 
    1 | 55 | 18
    1 | 55 | 18
    1 | 55 | 18
    2 | 55 | 18
    3 | 40 | 18
    4 | 55 | 18

    I need

    1 | 55 | 18
    2 | 55 | 18
    3 | 40 | 18
    4 | 55 | 18


    Can you help me?

    Here is table

    CREATE TABLE [dbo].[DipendentiAttivita](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDDipendente] [int] NULL,
    [IDMansione] [int] NULL,
    [IDAttivita] [int] NULL,
    [Attivo] [bit] NULL,
     CONSTRAINT [PK_DipendentiAttivita] PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    Thanks

    Sunday, September 13, 2020 5:28 PM

All replies

  • User452040443 posted

    Hi,

    Try:

    with CTE_RN as
    (
        select
            *,
            row_number() over(partition by IDDipendente, IDAttivita, IDMansione order by ID) as RN
        from DipendentiAttivita
    )
    
    delete from CTE_RN where RN > 1

    Hope this help

    Monday, September 14, 2020 1:01 AM
  • User1535942433 posted

    Hi dandi.box,

    As far as I think,without using CTE and ROW_NUMBER() you can just delete the records just by using group by with MAX function here is and example.

    DELETE
    FROM DipendentiAttivita
    WHERE ID NOT IN
    (
    SELECT MAX(ID)
    FROM DipendentiAttivita
    GROUP BY IDDipendente, IDMansione, IDAttivita)

    Result:

    Best regards,

    Yijing Sun

    Monday, September 14, 2020 1:39 AM