Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Posts
    13

    Unanswered: Is it possible deleting duplicate records in single query, HOW ?

    There is one table with 100 records , which contains 50 duplicate rows, means each row has one duplicate row.
    Now I wants to delete duplicate rows, using only one query ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does this table have a primary key?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    13

    No there is no primary key.

    How can a table have duplicate row if it has primary key in it.

    For eg., see the following snapshot of a table :

    select * from test ;

    emp_id ename esal
    --------------------------
    s1 Harry 1000
    s1 Harry 1000
    s2 Mary 3000
    s2 Mary 3000
    s3 Mac 2000
    s3 Mac 2000

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, lots of people disregard an autonumber or serial or identity primary key column when they talk about having "duplicate rows"

    but since this is not your situation, i recommend the following scenario:

    1. define a new table, with the appropriate primary key
    2. INSERT INTO newtable SELECT DISTINCT * FROM oldtable
    3. DROP oldtable
    4. RENAME newtable AS oldtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Posts
    13

    Thanks

    Thanks for your feedback. Even i did the same thing using tempd. But
    i think you didnt get my question as yet. I need to solve it in a single SQL query. Is it possible ?

  6. #6
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    The answer is NO in sybase. In some dbmses you have an internal row id, other than the unique or primary keys. But it still is not a true answer to your question.

    The simple fact, in order to delete, you need a unique SARG.

    Well theres one...

    If you really want it in ONE SQL, how about creating a stored proc and calling it... "sp_deleteduplicates". There you go thats ones command.

    Another option is to create unique index with ignore_dup_rows, but again its not completely what you want.

    Quote Originally Posted by goraksh

    How can a table have duplicate row if it has primary key in it.

    But
    i think you didnt get my question as yet. I need to solve it in a single SQL query.
    Whilst your intentions might be innocent, such statements is considered rude in a way. Theres always more than one solution to any problem and the original poster advised you of one of the ways it can be done, considering the pickle you are in.

    1. What you are asking looks like an interview question. Is it?
    2. If its not, and if you are thinking about cleanup, you already lost some ground in data integrity and hence your application is in a soup. So, spend more time on how to fix the core problem than bandaiding

  7. #7
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Just to add, programming is nothing but a million wrapper programs.

    So when you issue one command in a language, rdbms or even type a key-stroke in the keyboard it turns into multiple commands/instructions by the time its done.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •