say have 2 tables, records , recordsclean. both of these tables have 15 columns of information. want compare them using 1 unique column , identify unmatched results. once find unmatched results want remove them 1 table.
select email records id not in ( select email recordsclean ); now i'm using email in instance i'm going use more unique registration numbers. i'm testing email addresses. produces list of emails found in records not found in recordsclean. table records going hold historical data plan keep indefinitely recordsclean going fresh set of information. want compare full version of records new recordsclean , identify / remove matches recordsclean.
- data added
records. - new data added
recordsclean. - sql command runs , identifies matching records in both.
- sql command runs , removes matching records
recordscleanonly.
if want compare multiple columns , find rows don't match, use left join pattern rather not in.
select rc.* recordsclean rc left join records r on rc.col1 = r.col1 , rc.col2 = r.col2 , rc.col3 = r.col3 ... r.id null to delete them, change delete query.
delete rc recordsclean rc left join records r on rc.col1 = r.col1 , rc.col2 = r.col2 , rc.col3 = r.col3 ... r.id null 