Question 114168 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Populate Column with Data

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search