Delete rows based on group by-CodePudding

Let's say I have the following table:

Id | QuestionId
----------------
 1 | 'MyQuestionId'
 1 | NULL
 2 | NULL
 2 | NULL

It should behave like so

Find all the results of the same Id
If ANY of them has QuestionId IS NOT NULL, do not touch any rows with that Id.
Only if ALL the results for the same Id have QuestionId IS NULL, delete all the rows with that Id.

So in this case it should only delete rows with Id=2.

I haven't found an example for such a case anywhere. I've tried some options with rank, count, group by, but nothing worked. Can you help me?

CodePudding user response：

You can use an updatable CTE or derived table for this, and calculate the count using a window function.

WITH cte AS (
    SELECT t.*,
      CountNonNulls = COUNT(t.QuestionId) OVER (PARTITION BY t.Id)
    FROM YourTable t
)
DELETE cte
WHERE CountNonNulls = 0;

db<>fiddle

Note that this query does not contain any self-joins at all.