MySQL: Looking in different table to delete

skij
skij used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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 Developer
Commented:
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

Software Developer
Commented:
Obviously simpler:

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

Open in new window


Bye, Olaf.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial