Check All Occurrences and Update-CodePudding

I have a table of 200k rows that have 27k distinct person_identifier values in it. On each row there is a 'value' column and a 'complete' column. I am trying to update the 'complete' column to a yes if every single row for a person_identifier has a NOT NULL value.

Take this example:

person id	value	complete
1	23
1	22
1	NULL
2	88
2	22
3	46
3	78
4	NULL
4	NULL

In this example I would want complete to be 'yes' for person 2 and 3, but not for 1 and 4 since they have at least 1 NULL value

CodePudding user response：

You can solve your problem by inverting your reasoning. Follow these steps:

select all those "person id" values who have at least one NULL value
left join this selection with your table
update only those rows which have NULL as a result of the outer join

WITH cte AS (
    SELECT DISTINCT [person id]
    FROM tab
    WHERE [value] IS NULL
)
UPDATE tab
SET complete = 'yes'
FROM      tab 
LEFT JOIN cte ON tab.[person id] = cte.[person id]
WHERE cte.[person id] IS NULL;

Check the demo here.

CodePudding user response：

Use a CASE statement with COUNT in a subquery:

UPDATE a
SET complete = CASE 
                 WHEN (SELECT COUNT(person_id) FROM sampleTable WHERE value IS NULL AND person_id = a.person_id) > 0 THEN 'no'
                 ELSE 'yes'
               END
FROM sampleTable a

Result:

| person_id | value | complete  |
|-----------|-------|-----------|
| 1         | 23    | no        |
| 1         | 22    | no        |
| 1         | null  | no        |
| 2         | 88    | yes       |
| 2         | 22    | yes       |
| 3         | 46    | yes       |
| 3         | 78    | yes       |
| 4         | null  | no        |
| 4         | null  | no        |

Fiddle here.