Results 1 to 16 of 16

Thread: Delete duplicate records without using "rowid"

  1. #1
    Geek_Guest
    Guest

    Delete duplicate records without using "rowid"

    Question asked by visitor Joe

    Hi all! Good Evening.

    I would like to know if it is possible to delete duplicate records from a table with out using "rowid" using MYSQL only. Can use "rownum". Plz help me in this regard.

    Thanx in advance.
    Bye


  2. #2
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Delete duplicate records without using "rowid"

    I think you can't do that. Because Rownum is not linked with any record.


  3. #3
    Junior Member
    Join Date
    Feb 2007
    Answers
    17

    Re: Delete duplicate records without using "rowid"

    One example is here
    create table t1(col1 int, col2 int, col3 char(50))
    insert into t1 values (1, 1, 'data value one')
    insert into t1 values (1, 1, 'data value one')
    insert into t1 values (1, 2, 'data value

    SELECT col1, col2, count(*)
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1

    delete from t1
    where col1=1 and col2=1


  4. #4
    Expert Member
    Join Date
    Dec 2006
    Answers
    204

    Re: Delete duplicate records without using "rowid"

    Jaiprakash,
    we need to delete only one of those duplicated rows.
    but that delete statement will deletes both.


  5. #5
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: Delete duplicate records without using "rowid"

    Yes, it deletes both 1st and 2nd records.

    I think there is no solution without using ROWID.

    Lack of WILL POWER has caused more failure than
    lack of INTELLIGENCE or ABILITY.

    -sutnarcha-

  6. #6
    Junior Member
    Join Date
    Jul 2007
    Answers
    1

    Re: Delete duplicate records without using "rowid"

    Try this....
    delete from table where rowid=(select max(rowid) from table group by dup_field_name having count(*) > 2)



    Thanks...


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

    Re: Delete duplicate records without using "rowid"

    No, that is not possible in oracle..


  8. #8
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: Delete duplicate records without using "rowid"

    hi
    use rank() function to delete these duplicate records in oracle


  9. #9

    Re: Delete duplicate records without using "rowid"

    Quote Originally Posted by bhaski View Post
    hi
    use rank() function to delete these duplicate records in oracle
    could you please explain with example query.................


    Thanks


  10. #10
    Junior Member
    Join Date
    Oct 2008
    Answers
    11

    Re: Delete duplicate records without using "rowid"

    I think this is not possible in Oracle.


  11. #11
    Expert Member
    Join Date
    Nov 2008
    Answers
    300

    Re: Delete duplicate records without using "rowid"

    You can try using analytical function to delete duplciates in oracle


  12. #12
    Contributing Member
    Join Date
    Dec 2008
    Answers
    76

    Re: Delete duplicate records without using "rowid"

    Yes it is possible using analytic functions...
    I think this one will help you...

    select *
    from ( select a.*,
    row_number() over ( partition by column1 order by column2) r
    from table a)
    where r >1;

    delete from table where (c1,c2,...) in
    (Select * from table);


  13. #13
    Junior Member
    Join Date
    Jan 2009
    Answers
    2

    Re: Delete duplicate records without using "rowid"

    Yeah, It's possible in SQL Server 2005
    try this...
    This query delete duplicate records in one shot....

    delete from Table_Name where Column_Name in
    (
    select Column_Name from Table_Name
    group by Column_Name
    having (count(Column_Name)>1)
    )


  14. #14
    Junior Member
    Join Date
    Jan 2009
    Answers
    2

    Re: Delete duplicate records without using "rowid"

    Yeah, It's possible in SQL Server 2005
    try this...
    This query delete duplicate records in one shot....

    delete from Table_Name where Column_Name in
    (
    select Column_Name from Table_Name
    group by Column_Name
    having (count(Column_Name)>1)
    )


  15. #15
    Junior Member
    Join Date
    Jan 2009
    Answers
    9

    Re: Delete duplicate records without using "rowid"

    Try this

    delete from employees emp1 where rowid < (select max(rowid) from employees emp2 where emp1.employee_id=emp2.employee_id);


  16. #16
    Expert Member
    Join Date
    Nov 2008
    Answers
    300

    Re: Delete duplicate records without using "rowid"

    @Harishkshetty,

    The question is without using a rowid.


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