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
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'