MySQL: Looking in different table to delete

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?

CREATE TABLE `people` (
  `ContactID` int(11) NOT NULL AUTO_INCREMENT,
  `FName` char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Email` char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Status` char(20) COLLATE utf8_unicode_ci DEFAULT 'newlead',
  UNIQUE KEY `ContactID` (`ContactID`)
) ENGINE=InnoDB AUTO_INCREMENT=437 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `people` VALUES 
('72','Barbara','email@email.com','prospect'),
('133','Jack','user@email.com','prospect'),
('202','Sandra','info@example.com','prospect'),
('204','Jill','hello@email.com','customer'),
('251','Cheryl','me@example.com','customer'),
('296','Danielle','myemail@email.com','prospect'),
('326','Liam','help@example.com','prospect'),
('337','Mary','address@example.com','prospect'),
('352','Margie','m@example.com','prospect'),
('436','John','smile@email.com','customer');


CREATE TABLE `white_email` (
  `white_email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `white_email` VALUES 
('email@email.com'),
('me@example.com'),
('hello@email.com'),
('info@example.com');

Open in new window

LVL 10
skijAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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'
      )
Olaf DoschkeSoftware DeveloperCommented:
Well, in MySQL you can't do it exactly like that.

Subqueries

You cannot delete from a table and select from the same table in a subquery.

There's an ugly workaround in nesting the suquery as a subsubquery "as x":

delete from people
 where ContactID in (
       Select * from
       (Select ContactID 
       from people p2
       left join white_email w on p2.Email = w.white_email
       where w.white_email IS NULL
       and p2.status = 'prospect') as x
       ) 

Open in new window

Olaf DoschkeSoftware DeveloperCommented:
Obviously simpler:

delete from people
 where Status = 'prospect' AND Email NOT in (Select white_email from white_email) 

Open in new window


Bye, Olaf.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.