Fill null field with first previous null field

Asked

Viewed 136 times

0

Good afternoon,

I wanted to fill in the null fields with the first non-null previous field in a whole table. You know if you can do it?

Like this the table: Como esta a tabela

How should the table look: inserir a descrição da imagem aqui

postgresql

  • Would the records always be in the same order? If yes you can update this table using function. Could you include some of the original data in your question? It would be easier for me to send you an example.

  • Good morning Camilo, the data will always be like this. The null records just below will always receive the above product code that is not null. However, sometimes it has 2 descriptions, other 1, other 5. I made this form to be easier to understand. The original data would be too messy to understand.

1 answer

0

Since your table will always keep this sequence, a function can be done that first stores the code and when this field is null it uses the previously saved variable.

Below is an example:

do $$
declare
  vRegistros record;
  vCodigo integer;
begin
  --Executa a query e a cada iteração grava a tupla em vRegistros
  for vRegistros in select * from codigo_null order by id loop
    -- Se o campo codigo não for nulo a variável vCodigo receberá o seu valor
    if vRegistros.codigo is not null then
      vCodigo := vRegistros.codigo;
    else
    -- Quando o código for nulo irá atualizar o registro correspondente com o código gravado na iteração anterior
      update codigo_null
      set codigo=vCodigo
      where id=vRegistros.id;
    end if;
  end loop;
end
$$ language plpgsql;

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.