none
delete the duplicate records from the table

    Question

  • Hi, How I can delete the duplicate records from the table #1. Please help ASAP.

    create table #1 (p_code char(10),t_code char(4),t_code char(10))
    Insert into #1  ('EASP','WEDT','EDAR_01')
    Insert into #1  ('EASP','WEDT','EDAR_01')
    Insert into #1  ('EASP1','WEDT','EDAR_01')
    Insert into #1  ('EASP1','WEDT','EDAR_01')
    Insert into #1  ('EASP2','WEDT','EDAR_01')
    Insert into #1  ('EASP2','WEDT','EDAR_01')

    Thanks.

    Monday, July 08, 2013 8:37 PM

Answers

  • create table #1 (p_code char(10),t_code char(4),t_code1 char(10))
    Insert into #1 values ('EASP','WEDT','EDAR_01')
    ,('EASP','WEDT','EDAR_01')
    , ('EASP1','WEDT','EDAR_01')
    , ('EASP1','WEDT','EDAR_01')
    , ('EASP2','WEDT','EDAR_01')
    , ('EASP2','WEDT','EDAR_01')
    ;WITH mycte as
    (select * ,row_number() Over(Partition By p_code,t_code,t_code Order by p_code,t_code,t_code) rn from #1)
    Delete FROM mycte WHERE rn>1
    select * from #1
    drop table #1

    Monday, July 08, 2013 8:47 PM

All replies

  • create table #1 (p_code char(10),t_code char(4),t_code1 char(10))
    Insert into #1 values ('EASP','WEDT','EDAR_01')
    ,('EASP','WEDT','EDAR_01')
    , ('EASP1','WEDT','EDAR_01')
    , ('EASP1','WEDT','EDAR_01')
    , ('EASP2','WEDT','EDAR_01')
    , ('EASP2','WEDT','EDAR_01')
    ;WITH mycte as
    (select * ,row_number() Over(Partition By p_code,t_code,t_code Order by p_code,t_code,t_code) rn from #1)
    Delete FROM mycte WHERE rn>1
    select * from #1
    drop table #1

    Monday, July 08, 2013 8:47 PM
  • Your table has two t_code columns. However, fixing that and running something like

    WITH CTE1 AS

    (

    SELECT COUNT(1), p_code, t_code, t_code FROM [table #1] GROUP BY p_code, t_code, t_code

    HAVING COUNT(1) > 1

    )

    DELETE TOP (1) FROM [table #1] WHERE EXISTS (SELECT 1 FROM CTE1

    WHERE [table #1].p_code = CTE1.p_code AND [table #1].t_code =CTE1.t_code AND [table #1].t_code =CTE1.t_code)

    until there are no duplicates left should get you where you are going. If I had more time I might dump the CTE into table type I could loop on the existence of rows in.


    If you're happy and you know it vote and mark.

    • Proposed as answer by Selvaons Tuesday, July 09, 2013 6:49 AM
    Monday, July 08, 2013 8:58 PM