postgresql How to SELECT and UPDATE parent and child records from the same table?

Question

Hello I have the following records parents and children, you can tell by the parent_load_record_id column.

The thing is I need those records who have children and their children to have false in the is_unique field, since they are not unique anymore.

The parents who have no children have to stay as is_unique = true

id	document_number	document_status	is_unique	parent_load_record_id
276983	49515	INFORMED	true
1060396	251013	INFORMED	true
177836	0000221	INFORMED	true
207775	0000221	APPROVED	true	177836
19342	0000221	RESULTANT	true	177836
19721	000456	INFORMED	true
19786	000456	EDITED	true	19721

The problem I'm having is. I'm trying to get all the parents with this query and I always get zero results (this query I use to check if my update actually worked)

SELECT 
l.id,
l.document_number,
l.document_status,
l.is_unique,
l.parent_load_record_id
FROM load_record l
WHERE l.parent_load_record_id IS NULL
AND l.id NOT IN(SELECT DISTINCT lr.parent_load_record_id FROM load_record lr);

I use this query to update all children

UPDATE load_record SET is_unique = false WHERE parent_load_record_id IS NOT NULL

The problem comes when I have to UPDATE all parents with children

UPDATE load_record SET 
is_unique = false 
WHERE parent_load_record_id IS NULL 
AND id IN(SELECT DISTINCT lr.parent_load_record_id FROM load_record lr)

The Update is not really doing what it needs to do, just like the SELECT is there a reason for it? or is there a better way to do it?

Answer 1

The problem is that SELECT DISTINCT lr.parent_load_record_id FROM load_record lr returns a NULL row, which leads to id NOT IN (…) returning NULL as well (demo). You can either filter them:

… AND l.id NOT IN (SELECT DISTINCT lr.parent_load_record_id FROM load_record lr WHERE lr.parent_load_record_id IS NOT NULL);

^{(updated demo)}

or just use a WHERE NOT EXISTS clause:

… AND NOT EXISTS (
  SELECT *
  FROM load_record lr
  WHERE lr.parent_load_record_id = l.id
)

^{(updated demo)}