PostgreSQL Populate Column with Data-CodePudding

I try to populate null-rows in a table with data from the same table. Here is my code:

create table public.testdata(
id INTEGER,
person INTEGER,
name varchar(10));

insert into testdata (id, person,name) VALUES ( 1,1,'Jane' ), ( 2,1,'Jane' ), ( 3,1,NULL ), ( 4,2,'Tom' ), ( 5,2,NULL );


select * from testdata;

enter image description here

Basically i would like to have name 'Jane' in the 3rd row and name 'Tom' in the 5th.

Here is the asnswer which i have found online to a simmilar problem:

Update testdata
SET name = COALESCE(a1.name, b1.name) 
FROM testdata a1 
JOIN testdata b1 
    on a1.person = b1.person 
    and a1.id <> b1.id 
where a1.name is NULL;

But if i run this code, i get name 'Jane' in every column, which is not what i want. I appreciate any help and suggestions.

CodePudding user response：

Example for you:

select t1.id, t1.person, t2.name from testdata t1 
left join 
(
    select distinct person, name from testdata
    where name is not null
) t2 on t1.person = t2.person

CodePudding user response：

Get the person (id?) and the desired name via a CTE. Then use the results to update names. So (see demo):

with namer (person, name) as 
     ( select distinct on (person) 
              person, name 
         from testdata
        where name is not null
        order by person, name
     ) 
update testdata d1
   set name = (select n1.name
                    from namer n1 
                   where n1.person = d1.person
                 ) 
 where d1.name is null;

NOTE: Demo contains additional rows where the entry sequence of the rows is not ideal. And not all person values have associated name.