Simple deuping

  • Hiya i want to do a simple self-join on a table and delete the duplicates.. if i use

    select  a.lastname, a.firstname,  a.id from theatre a

    inner join theatre b

    on a.lastname = b.lastname

    where  a.lastname is not null and a.firstname is not null

    and a.lastname <> '' and a.firstname <> ''

    and a.firstname = b.firstname

    and a.id <> b.id

     

    then it brings back both original and duplicate whereas i only want to select one to delete so i can do somet6hing like this

     

    delete from theatre where id in

    (

    a.id from theatre a

    inner join theatre b

    on a.lastname = b.lastname

    where  a.lastname is not null and a.firstname is not null

    and a.lastname <> '' and a.firstname <> ''

    and a.firstname = b.firstname

    and a.id <> b.id)

     

    any thoughts?

     

    thanks!

  • SET NOCOUNT ON

    DECLARE @theatre TABLE

    (

    [ID] INT IDENTITY,

    LastName VARCHAR(25),

    FirstName VARCHAR(25)

    )

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L1111', 'F1111')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L2222', 'F2222')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L3333', 'F3333')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L4444', 'F4444')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L6666', 'F6666')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L7777', 'F7777')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L8888', 'F8888')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L9999', 'F9999')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L0000', 'F0000')

    --Duplicates

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L7777', 'F7777')

    -- List Duplicates

    SELECT LastName, FirstName, MAX([ID])

    FROM

     @theatre

    GROUP BY LastName, FirstName

    HAVING COUNT(*) > 1

    --DELETE Duplicates

    DELETE @theatre

    WHERE

     [ID] IN

     (

      SELECT MAX([ID])

      FROM

       @theatre

      GROUP BY LastName, FirstName

      HAVING COUNT(*) > 1)

    SELECT * FROM @theatre

    Regards,
    gova

  • This will work for ANY No of duplicates and will keep the first row with the MIN(ID)

     

    SET NOCOUNT ON

    DECLARE @theatre TABLE

    (

    [ID] INT IDENTITY,

    LastName VARCHAR(25),

    FirstName VARCHAR(25)

    )

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L1111', 'F1111')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L2222', 'F2222')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L3333', 'F3333')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L4444', 'F4444')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L6666', 'F6666')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L7777', 'F7777')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L8888', 'F8888')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L9999', 'F9999')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L0000', 'F0000')

    --Duplicates

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L7777', 'F7777')

    INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')

    -- List Duplicates

    SELECT LastName, FirstName, Count(*) As Duplicates

    FROM

     @theatre

    GROUP BY LastName, FirstName

    HAVING COUNT(*) > 1

    --DELETE Duplicates

    DELETE A

    FROM @theatre A INNER JOIN

    (SELECT MIN(ID) ID,LastName,FirstName

    FROM @theatre

    GROUP BY LastName, FirstName

    HAVING COUNT(*) > 1) B

    ON A.LastName=B.LastName and A.FirstNAme=B.FirstName

    WHERE A.ID>B.ID

    SELECT * FROM @theatre


    Kindest Regards,

    Vasc

  • ah didnt think to use count.. very good thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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