SQL query comparing column between 2 found sets

Question

I have one table, we'll call it BUS and it has two columns called DATE and TAG

I want to do two finds, as shown below

Query 1

select * 
from BUS
where DATE < '2009-03-01'

&

Query 2

select * 
from BUS
where DATE >= '2009-03-01'

I want to compare these two found sets, and delete any rows where the TAG from query 1 equals the TAG from query 2.

I tried an INTERSECT but I don't think that's the correct way. I can't wrap my head around this kind of query. Any advice?

Isn't there a primary key in this table? – forpas Feb 27 '19 at 19:34 — forpas, Feb 27 '19 at 19:34

score 1 · Accepted Answer · answered Feb 27 '19 at 19:27

1

Here is the Query.

delete
from BUS
where DATE < '2009-03-01' 
and tag in 
(select tag from BUS where DATE >= '2009-03-01')

answered Feb 27 '19 at 19:27

Hasnain Bukhari

458
2
6
23

score 1 · Answer 2 · answered Feb 27 '19 at 19:51

1

You can simply do:

select tag
from BUS
group by tag
having min(date) < '2009-03-01' and
       max(date) >= '2009-03-01';

answered Feb 27 '19 at 19:51

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Feb 27 '19 at 19:35

Another solution to your problem:

-- SELECT b1.*
DELETE b1
FROM BUS b1
  INNER JOIN BUS b2 ON b1.TAG = b2.TAG
WHERE b1.DATE < '2009-03-01' AND b2.DATE >= '2009-03-01';

Before executing the query you can comment out the DELETE statement, uncomment the SELECT statement and check if those are the rows you want gone.

Similar examples can be seen in the documentation (scroll down a bit) and some other SO questions:

SQL query comparing column between 2 found sets

3 Answers3