In table T, I'm trying to delete all records in a groups having same value of A, but only if all members of this group have B set to 'x'.
Given the Table T:
+-------+--------+
| A | B |
+-------+--------+
| 2 | '' |
| 2 | 'x' |
| 2 | '' |
| 8 | 'x' |
| 8 | 'x' |
| 15 | '' |
| 15 | '' |
+-------+--------+
The two records with A == 8 have to be deleted as all two of them have B==1. The group of A==2 has mixed value of B so it stays. And group of A==15 doesn't have all of it's B equal to 1 it also stays.
Is this possible to do by one query?
If not, any other way that is fast enough for a table with a lot of records?
you can try this query:
delete from T
where A in (
select A
from T
group by A
having sum(B) = count(*)
)
if column b can contain non 0/1 values, you can add additional conditions:
having sum(B) = count(*) and min(b)=1 and max(b)=1
if you can't use numeric values, you can just use min/max, like
having min(b)='x' and max(b)='x'
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments