Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl(
   id   int  NOT NULL 
 , flag boolean
);

INSERT INTO tbl VALUES
 (1,True)
,(1,True)
,(1,NULL)
,(1,True)
,(1,NULL)
,(2,False)
,(2,False)
,(2,False)
,(2,NULL)
,(2,NULL)
,(3,NULL)  -- added test cases
,(3,NULL)
,(4,true)
,(4,true)
,(5,false)
,(6,null);


CREATE TABLE



INSERT 0 16


-- effective target values per id
SELECT DISTINCT ON (id)
       id, flag
FROM   tbl
ORDER  BY id, flag;


id
flag


1
t

2
f

3
null

4
t

5
f

6
null



SELECT 6


-- efficient UPDATE
UPDATE tbl t
SET    flag = t1.flag
FROM  (
   SELECT DISTINCT ON (id)
          id, flag
   FROM   tbl
   ORDER  BY id, flag
   ) t1                      -- avoid repeated computation for same id
WHERE  t.id = t1.id
AND    t.flag IS NULL        -- avoid costly no-op updates
AND    t1.flag IS NOT NULL;  -- avoid costly no-op updates;

UPDATE 4


TABLE tbl;


id
flag


1
t

1
t

1
t

2
f

2
f

2
f

3
null

3
null

4
t

4
t

5
f

6
null

1
t

1
t

2
f

2
f



SELECT 16

id	flag
1	t
1	t
1	t
2	f
2	f
2	f
3	null
3	null
4	t
4	t
5	f
6	null
1	t
1	t
2	f
2	f