Avatar of skij
skij
Flag for Canada 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');

Open in new window

MySQL Server

Avatar of undefined
Last Comment
Olaf Doschke

8/22/2022 - Mon
PortletPaul

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'
      )
SOLUTION
Olaf Doschke

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Olaf Doschke

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck