bars96 bars96 - 13 days ago 6
MySQL Question

How to delete rows in Table1 where COUNT of WHERE in Table2 is greater than 0?

I have [Table1] with user ID and [Table2] with the same user ID (left join) and column "open" with number of readed messages. I want to delete all users from [Table1] which has all rows in [Table2] with "open" column value < 1. I trying to select them first, but it gives SQL errors:

Trying 1:

SELECT pg_acymailing_subscriber.*,
COUNT(DISTINCT case when pg_acymailing_userstats.open > 0 end) as readc
LEFT JOIN `pg_acymailing_userstats`
ON pg_acymailing_subscriber.subid=pg_acymailing_userstats.subid
WHERE pg_acymailing_subscriber.subid=24 AND readc > 0;


Trying 2:

SELECT *
FROM `pg_acymailing_subscriber`
LEFT JOIN `pg_acymailing_userstats`
ON pg_acymailing_subscriber.subid=pg_acymailing_userstats.subid
WHERE COUNT(DISTINCT case when pg_acymailing_userstats.open > 0 /*If user doesn't have rows with "open" column value > 0, we select it if whole rows numer is 0*/
end) < 1
and pg_acymailing_subscriber.subid=24;

Answer

If performance is not an issue, it's probably easiest to build this with a subquery.

My understanding of your logic is that you want to delete all users from [Table1] that do not have any rows in [Table2] where open > 0, is that a correct paraphrasing of your requirement?

select * from pg_acymailing_subscriber where subid not in
( select subid from pg_acymailing_userstats where open > 0 )

If that shows the correct records, delete as follows:

delete from pg_acymailing_subscriber where subid not in
( select subid from pg_acymailing_userstats where open > 0 )