Results 1 to 16 of 16

Thread: How to delete duplicate rows in a table

  1. #1

    How to delete duplicate rows in a table

    Hi,
    this is saritha.


  2. #2
    Contributing Member
    Join Date
    Sep 2007
    Answers
    35

    Re: How to delete duplicate rows in a table

    hi

    by using the rowid we can delete the duplicate rows.

    ex:using emp table

    delete from emp where rowid not in(select max(rowid) from emp group by empno);


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

    Re: How to delete duplicate rows in a table

    You can also try this

    Delete from emp e
    where rowid>(select min(rowid) from emp
    where e.empno=empno)



  4. #4
    Junior Member
    Join Date
    Nov 2007
    Answers
    22

    Angry Re: How to delete duplicate rows in a table

    hi

    by using the rowid you can delete the duplicate rows from the table

    beacause rowid is the unique for the every rows so that by using this you can do it .

    delete from [table_name] where rowid not in(select max(rowid) from [table_name] group by [field_name]);

    regards


  5. #5
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: How to delete duplicate rows in a table

    You can also do in the following ways
    1)delete from emp x
    where rowid <> (select max(rowid) from emp y
    where x.empno = y.empno)
    2)delete from emp x
    where exists (select * from emp y
    where x.empno = y.empno and x.rowid > y.rowid)


  6. #6
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: How to delete duplicate rows in a table

    Try this
    Delete from table_name a
    where rowid>(select min(rowid) from
    Same_table_name b where b.pk_column_1=a.pk_column_1
    and b.pk_column_2=a.pk_columb_1);


  7. #7
    Junior Member
    Join Date
    Oct 2007
    Answers
    1

    Re: How to delete duplicate rows in a table

    There are two ways of Deleting the Duplicate rows Based on which Row you want to keep as it is .

    1]If you want to delete rows ,keeping minimum rowid row then use Following query ,considering Emp table having empno as key

    Delete from emp e
    where rowid>(select min(rowid) from emp
    where empno=e.empno)

    1]If you want to delete rows keeping maximum rowid row , then use Following query

    Delete from emp e
    where rowid<(select max(rowid) from emp
    where empno=e.empno)


  8. #8

    Re: How to delete duplicate rows in a table

    if I have 100 columns in the table?
    delete duplicate rows but can not delete a record in the same name and different data.


  9. #9

    Re: How to delete duplicate rows in a table

    I tried following ways

    delete from colum_3 a
    2 where a.rowid >
    3 (select min(b.rowid) from colum_3 b
    4 where a.name = b.name
    5 and a.age = b.age);

    but i want exact result with deleting different data rows


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

    Re: How to delete duplicate rows in a table

    your question is not clear ,can you pass more information.


  11. #11

    Re: How to delete duplicate rows in a table

    Actually i want delete duplicate rows in the table. If I have 100 columns and 1000 rows in the table how will delete the duplicate rows.
    suppose employee table can have 100 columns and 1000 rows and same name present in the table multiple employe can have diffrent data. but i want keep the employe and i delete only duplicate rows of original one.


  12. #12

    Re: How to delete duplicate rows in a table

    Table can have a following data: name age location .... Department email ---------- ---------- --------- --------- -------- arun 25 chennai .... Hr sample@sample.com arun 25 chennai .... Hr sample@sample.com arun 25 chennai .... Hr sample@sample.com murugan 27 trichy .... Sales sample1@sample.com mani 28 chennai .... Software sample2@sample.com mani 35 trichy .... Marketing sample3@sample.com kathir 29 salem .... Software sample4@sample.com arun 33 chennai .... Manager arun@sample.com arun 33 chennai .... Manager arun@sample.com output should be name age location .... Department email ---------- ---------- --------- --------- -------- arun 25 chennai .... Hr sample@sample.com murugan 27 trichy .... Sales sample1@sample.com mani 28 chennai .... Software sample2@sample.com mani 35 trichy .... Marketing sample3@sample.com kathir 29 salem .... Software sample4@sample.com arun 33 chennai .... Manager arun@sample.com


  13. #13

    Re: How to delete duplicate rows in a table

    table can have a 100 column and 1000 rows. i want delete duplicate rows only. the table can have a employee data
    the employee same name present in the table but different data and also present duplicate records in the table same name and same data. i want delete only duplicate rows. i can not delelete same name and same age and same department. we can only delte duplicate of original rows.
    i tried above mentioned query. help me how will delete duplicate rows?.


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

    Re: How to delete duplicate rows in a table

    For deleting duplicate records, you can use the below query:

    DELETE FROM emp where rowid NOT IN (SELECT MAX(rowid) FROM emp Group by empid);

    The above query will delete the duplicate records which has same empid

    Incase if you want to check for all the columns then try this query:

    delete from emp where rowid not in (select max(rowid) from emp group by id,name,no)

    My emp table had 3 columns id,name,no and you need to group by all the columns in order to check if any column contains different value or not.if yes then that record will not be considered as duplicate and will not be deleted. The above query will delete the record only when all the columns have same value


  15. #15

    Re: How to delete duplicate rows in a table

    this query only possible when we are using 3 or 4 columns but when i used 100 or more column how it is possible group by all the 100 columns.
    i already tried this query any way thanks for your time .


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

    Re: How to delete duplicate rows in a table

    You will need to GROUP BY all the columns in case if you are looking at different value for any of the column. There is no other way around.


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