i have the following problem:
i got 2 tables with much data (table1: 170K records, table2: 7K records), both tables have email fields i want to compare with each other.
for better results i added to both tables a field “email_compare” and put an index on it.
now i want to delete (or mark) all records from table1 that are in table2 (compare by email).
everything i tried so far ended up in waiting for HOURS until i killed the process.
UPDATE table1 t1
LEFT JOIN table2 t2 ON t1.email_compare=t2.email_compare
WHERE t2.email_compare IS NOT NULL
WHERE email_compare IN (SELECT email_compare FROM table2)
UPDATE table1 SET delete_marker=1 WHERE (SELECT 1 FROM table2
if its really impossible to speed the above ones up,
i think it would be better to begin with table2. but how can i execute an update to table1 for each entry in table2?
or any other suggestions?
thank you in advance )