SQL delete query

How would you write a query to delete all customers who have visited more than 2 unique cities?


Table    Customer

CustId   Name
101      Name1
102      Name2
103      Name3
104      Name4
105      Name5
106      Name6
107      Name7
...      ...




Table CustomerVisit

CustId   VisitDate     VisitedCity
101      1/1/2017      City1
101      1/5/2016      City2
101      1/3/2016      City3
102      1/1/2016      City1
102      1/5/2016      City2
102      1/3/2016      City1
102      1/1/2016      City2
103      1/1/2017      City1
104      1/1/2017      City2
105      1/1/2017      City4
...     ...                     ...
quasar_eeAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
You can try this:

DECLARE @Customer TABLE (
	CustId		INTEGER,
	Name		VARCHAR(20)
)

INSERT INTO @Customer ( CustId, Name )
VALUES 
(101, 'Name1'),
(102, 'Name2'),
(103, 'Name3'),
(104, 'Name4'),
(105, 'Name5'),
(106, 'Name6'),
(107, 'Name7')


DECLARE @CustomerVisit TABLE (
	CustId		INTEGER,
	VisitDate	DATETIME,
	VisitedCity	VARCHAR(20)
)

INSERT INTO	@CustomerVisit ( CustId, VisitDate, VisitedCity )
VALUES 
(101, '1/1/2017','City1'),
(101, '1/5/2016','City2'),
(101, '1/3/2016','City3'),
(102, '1/1/2016','City1'),
(102, '1/5/2016','City2'),
(102, '1/3/2016','City1'),
(102, '1/1/2016','City2'),
(103, '1/1/2017','City1'),
(104, '1/1/2017','City2'),
(105, '1/1/2017','City4')


SELECT * FROM @Customer
SELECT * FROM @CustomerVisit

DELETE FROM @Customer
WHERE CustId IN (
	SELECT CustId FROM @CustomerVisit GROUP BY CustId HAVING COUNT(DISTINCT VisitedCity) > 2
)

SELECT * FROM @Customer

Open in new window

0
 
quasar_eeAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.