Post reply

How to find and remove duplicate field values from a table?

  • January 3, 2012 at 10:44 pm

    #252812

    Hi there,

    I have a table that is used to service a email distribution Listsystem, I need to remove dupicate email fields from different email lists.

    Essentially I need a query that finds equivalent fields 'email' within the one table but removes the equivalnet 'email field where the qualifying field, eg 'Listname', so the email only goes to one particular list not two. To do this manually would be impossible consider we have over 50,000 emails!

    Any tips would be great, thanks.

  • January 3, 2012 at 10:47 pm

    #1428088

    Can you post some sample data?

    Shatrughna

  • January 3, 2012 at 10:51 pm

    #1428091

    Try this article on how to do it using a CTE.

    http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 3, 2012 at 10:59 pm

    #1428094

    Check this blog post[/url] to deal with Duplicate records in a table.

    ~manoj | LinkedIn | Facebook | YouTube
    Blog: SQLwithManoj.com

  • January 3, 2012 at 11:01 pm

    #1428095

    shatrughna (1/3/2012)

    Can you post some sample data?

    And it actually has more than 10 rows of test data. Nicely done, Jason.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 3, 2012 at 11:13 pm

    #1428099

    Jeff Moden (1/3/2012)

    And it actually has more than 10 rows of test data. Nicely done, Jason.

    Thanks Jeff

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 4, 2012 at 4:03 pm

    #1428746

    OK here is some sample data:

    Email Name List Name Member ID

    f.leffer@gmail.com Fiona Leffer enews 280009

    f.leffer@gmail.com NULL kidsclub 363184

    f.leffer@gmail.com NULL members 358305

    x.bridge@ccgs.edu.au Tony Bridge education 182675

    The issue is the email address that is a member of 'enews' should not get 'kidsclub' and 'members' emails so those

    two need to be removed.

    thanks!

  • January 4, 2012 at 4:21 pm

    #1428752

    Can a subscriber have a subscription to one of those two and not subscribe to enews?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 4, 2012 at 5:10 pm

    #1428759

    No a members that is subscribe to members should not be subscribed to e-news as they both get the same emails.

    I know it's a stupid business, but that's how they roll!

  • January 4, 2012 at 6:06 pm

    #1428762

    I'm guessing there are some normalization issues based on what you've provided. If you would like additional help, it would be helpful to us if you provide more specific DDL with some ready-to-use inserts of sample data.

    create table dbo.TestTable

    ( RecNo int identity(1,1) primary key

    , Email varchar(256)

    , name varchar(200)

    , ListName varchar(100)

    , MemberID int );

    insert into dbo.TestTable values

    ('f.leffer@gmail.com', 'Fiona Leffer', 'enews', 280009),

    ('f.leffer@gmail.com', NULL, 'kidsclub', 363184),

    ('f.leffer@gmail.com', NULL, 'members', 358305),

    ('x.bridge@ccgs.edu.au', 'Tony Bridge', 'education', 182675);

    Now, to delete the 'kidsclub' and 'members' entries for the emails that are also associated with 'enews', you can do something like the following:

    with cteEnews as

    (

    select distinct Email

    from dbo.TestTable

    where ListName = 'enews'

    )

    delete t

    from cteEnews as cte

    join dbo.TestTable as t

    on t.Email = cte.Email

    where t.ListName in ('kidsclub', 'members');

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

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