troubleshooting Question

MySQL: Looking in different table to delete

Avatar of skij
skijFlag for Canada asked on
MySQL Server
3 Comments2 Solutions45 ViewsLast Modified:
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');
ASKER CERTIFIED SOLUTION
Olaf Doschke
Software Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros