Post reply

Deleting duplicate Fields from a Table

  • January 9, 2008 at 3:39 am

    #154050

    Hi im having one Table called employee.. but there is no primary key column.. i would like to remove the duplicate field..

    in the bellow example

    Name 'Lissa' is repeating thrice

    'Mike' is repeating twice

    'Jack' is repeating twice

    'Cathy' is also repeating twice

    i dont want repeat these name again and again.. it should appear only once

    id Name empssn

    -- ----- ------

    1 Jack 555-55-5555

    2 Joe 555-56-5555

    3 Fred 555-57-5555

    4 Mike 555-58-5555

    5 Cathy 555-59-5555

    6 Lisa 555-70-5555

    7 Jack 555-55-5555

    8 Mike 555-58-5555

    9 Cathy 555-59-5555

    10 Lisa 555-70-5555

    11 Lisa 555-70-5555

    i have used the following query, but its not working

    delete from employee

    where (rowid, empssn)

    not in

    (select min(rowid), empssn from employee group by empssn);

    plz help to solve this...........

  • January 9, 2008 at 3:56 am

    #766083

    This should work

    set xact_abort on

    begin tran

    select distinct id, [Name], empssn into #tempEmployees

    truncate table employees

    insert employees(id, [Name], empssn)

    select id, [Name], empssn

    from #tempEmployees

    drop table #tempEmployees

    commit

    HTH

    Piotr

    ...and your only reply is slàinte mhath

  • January 9, 2008 at 5:19 am

    #766102

    Thank you very much for ur response...

    Without temp Table is it possible?..

    can u plz explain me

  • January 9, 2008 at 10:30 am

    #766320

    DELETE FROM e

    FROM employee e

    JOIN employee f ON e.Name = f.Name AND e.empssn = f.empssn

    WHERE e.id < f.id

  • January 10, 2008 at 7:39 am

    #766651

    Ramesh (1/9/2008)

    Thank you very much for ur response...

    Without temp Table is it possible?..

    can u plz explain me

    Why do people always ask that? What's so poison about temp tables. Proper use of temp tables can make lightning fast code. Hell, Microsoft uses temp tables in many of their system stored procedures... 😉

    --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 11, 2008 at 1:56 am

    #766992

    Hi Ramesh,

    Try the follwoing query

    Select * from Employee

    where slno

    not in( select min(slno) from Employee group by name,ssn)

    If the above select gives your duplicate records then replace the select statement with Delete.

    Be who you are and say what you feel,
    because those who mind don't matter
    and those who matter don't mind...

  • January 11, 2008 at 7:31 am

    #767096

    Including name in the sub-query will not guarantee unique SSN's. 😉

    Still, I'd like to know... not that I'd do it with a Temp Table, but what's so important about avoiding Temp Tables on something like this? Is it because you guys are doing it from a GUI or some interface software or what?

    --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 13, 2008 at 9:01 pm

    #767555

    Hi Jeff,

    There is no harm by using Temp tables. I also use for most of my complex update & select queries.

    May be if somebody is not using a stored procedure and implementing direct SQL query from GUI then they may avoid Temp tables to make their query simpler.

    Regards,

    Praveen

    Be who you are and say what you feel,
    because those who mind don't matter
    and those who matter don't mind...

  • January 14, 2008 at 5:34 am

    #767639

    Sure, Praveen... I just said that 😉 but thanks for the feedback.

    And, my bad... Ramesh was the one that said it needed to be done without a temp table and I should have specifically asked him...

    How 'bout it, Ramesh... why did you specifically ask for this to be done without a temp table?

    --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

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

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