asked on 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');
MySQL Server
Last Comment
Olaf Doschke
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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'
)