Using MySQL, how can I delete all from `people` where `Status` = 'prospect' and the email is NOT in the column `white_email` from the white_email table?
select *
from people p
left join white_email w on p.Email = w.white_email
where w.white_email IS NULL
and p.status = 'prospect'
Use a LEFT JOIN and then where that join produces NULL as a result you have the rows in the people table not in the white list, then add the other critieria regarding the people attributes.
Use a select query first, confirm you have the right rows, then perform a delete.
delete from people
where ContactID in (
select ContactID
from people p
left join white_email w on p.Email = w.white_email
where w.white_email IS NULL
and p.status = 'prospect'
)
from people p
left join white_email w on p.Email = w.white_email
where w.white_email IS NULL
and p.status = 'prospect'
Use a LEFT JOIN and then where that join produces NULL as a result you have the rows in the people table not in the white list, then add the other critieria regarding the people attributes.
Use a select query first, confirm you have the right rows, then perform a delete.
delete from people
where ContactID in (
select ContactID
from people p
left join white_email w on p.Email = w.white_email
where w.white_email IS NULL
and p.status = 'prospect'
)