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

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 )

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.

LVL 52

PortletPaulEE Topic AdvisorCommented: 2016-01-21

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

LVL 31

Olaf DoschkeSoftware DeveloperCommented: 2016-01-21

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":

Obviously simpler:

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

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

MySQL: Looking in different table to delete

Who is Participating?