INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

delete duplicate rows from large table

delete duplicate rows from large table

(OP)
I have two tables table1 and table2. Each is over 1 million rows. Table2 has some records that are also in table1. I need to delete the rows from table2 that are also in table1. I've tried this:

CODE

delete * from table2 where record in (select * from table1 where record like "1%"); 

Both tables only have one column called record. If I run the above command it just chugs for hours and never finishes.

RE: delete duplicate rows from large table

Hi

Avoid in with sub-select. Take advantage of MySQL's delete syntax which allows join with other tables :

CODE --> MySQL

delete from table2

using table1
inner join table2

where table1.record=table2.record
and table1.record like '1%'; 

Feherke.
http://feherke.github.com/

RE: delete duplicate rows from large table

(OP)
I get the same results with this query. Just runs forever and never does anything.

RE: delete duplicate rows from large table

Hi

There is another way, in two steps. Should also be faster, if the previous one not helped, probably neither this one will. This supposes that table2.record has no primary or unique key set :

CODE --> MySQL

update table2,table1

set table2.record=null

where table1.record=table2.record
and table1.record like "1%";

delete from table2

where record is null; 

Feherke.
http://feherke.github.com/

RE: delete duplicate rows from large table

(OP)
I think it's doing the same thing. What can I do with my data to make it work? Any tuning I can do the the server? Indexing? I'm running this in dev on Windows 7 64-bit 8GB RAM, MySQL 5.5.12. The two tables have just one column containing a list of words with a maximum length of 20 characters. Table2 has a bunch of entries that should already be in table1. I need to delete the duplicates from table2. I'm running the queries from the mysql CLI.

RE: delete duplicate rows from large table

Hi

MySQL knowledge is mostly theoretical as I use it rarely. So this may be plain stupid idea, but first I would try to check the database for damages. ( I think mysqlcheck serves that purpose. )

Feherke.
http://feherke.github.com/

RE: delete duplicate rows from large table

Try this:

CODE

delete from table2, table1 where exists 
  (select 1 from table1 where table2.record = table1.record); 

Regards,

Ed

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close