I want to remove records from another table that have those phone numbers in them

I have a table with a list of phone numbers.

I want to remove records from another table that have those phone numbers in them.

Something like this, but I know this isn't correct:

delete from db2013 where phone = (SELECT phone from db2010)

 

DELETE a
FROM db2013 a
INNERJOIN db2010 bON b.phone = a.phone

Code (markup):

I think this should work - however, you might need a filter condition, but I think this should work fine for your purpose

 

Sorry doesn't work for me.... I appreciate your help

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INNERJOIN db2010 bON b.phone = a.phone' at line 1

 

Of course it doesn't. There's no such thing as "bON".

 

Ah, my bad, missing space there. Change it to this and see if it works:

DELETE a
FROM db2013 a
INNERJOIN db2010 b ON b.phone = a.phone

Code (markup):

However, it might not - I'm not sure if this works on MariaDB (although MySQL / MariaDB is mostly (98%) compatible)

 

Looks like that is working, there are about 80 million records, so might be a while before I know for sure

 

80 mil records would be hard to delete, unless you do them in batches. Even if you do a million at a time you'll probably have issues. Were you able to delete some / all of them?

 

Where would I add a limit in the query.

Also if I did limit it, how could I do the next batch without going thru the old ones again?

Is it as simple as adding this in the query somewhere?

LIMIT 100000

LIMIT 100001,100000

 

I am not good at any of that myself, @PoPSiCLe would tell you exactly how it should be done in that particular instance. I don't think LIMIT is the right thing to use in your case. It would be easy to do if the phone #'s had some kind of count. Let's say you have a column that's named "count", then you could do a clause: WHERE count >= '1' AND count <= '50000'

 

https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes should help.