Results 1 to 8 of 8

Thread: How can we delete the duplicate data from a database table?

  1. #1
    Contributing Member
    Join Date
    Apr 2008
    Answers
    31

    Post How can we delete the duplicate data from a database table?

    To delete the duplicate data we may use the temporory tables, but with out using any teporory tables can we delete the duplicated data, if how it is possible?

    Last edited by nageshkota; 04-23-2008 at 09:14 AM.

  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: How can we delete the duplicate data from a database table?

    Kindly post what you have tried so far for further discussion on the topic.


  3. #3
    Junior Member
    Join Date
    Jun 2008
    Answers
    1

    Re: How can we delete the duplicate data from a database table?

    ;with DelDup as (select row_number() over (partition by
    ID order by ID) as RowNo from duplicateTest )
    Delete from DelDup where RowNo> 1


  4. #4
    Banned
    Join Date
    Jun 2008
    Answers
    3

    Re: How can we delete the duplicate data from a dataobase table in Oracle ?

    DELETE FROM emp WHERE rowid NOT IN (SELECT max(rowid) FROM emp GROUP BY id);


  5. #5
    Junior Member
    Join Date
    Jun 2008
    Answers
    5

    Re: How can we delete the duplicate data from a database table?

    To delete the duplicate data you can use this sql query: method 1 :delete from categories where categories.id not in (select min(c.id) from categories as c where categories.category_name = c.category_name) method 2: set rowcount syntax for set rowcount is as follows: set rowcount { number | @number_var } set rowcount 3 delete from users where firstname = n'elvis' and lastname = n'presley' set rowcount 0 -- (3 row(s) affected)


  6. #6

    Re: How can we delete the duplicate data from a database table?

    Quote Originally Posted by nageshkota View Post
    To delete the duplicate data we may use the temporory tables, but with out using any teporory tables can we delete the duplicated data, if how it is possible?
    DELETE FROM wikis_details
    WHERE wiki_id in
    (SELECT wiki_id
    FROM wikis_details
    GROUP BY wiki_id
    HAVING count(wiki_id)>1)


  7. #7
    Junior Member
    Join Date
    Jul 2008
    Answers
    1

    Re: How can we delete the duplicate data from a database table?

    using rowid


  8. #8
    Junior Member
    Join Date
    Sep 2008
    Answers
    1

    Re: How can we delete the duplicate data from a database table?

    We can use Common table Expression

    ;With empcte(id,sal,Ranking)
    as
    (Select id,sal,Ranking=Dense_Rank() over (Partition by id,sal
    order by newid() asc) from employee)
    Delete * from empcte where Ranking>1

    Regards
    Smitha


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact