PostgreSQL - Query-based Updates

postgresql-query-based_updates.sql

/**
 *
 * Example 0.
 *
 */
update trash.arqueta_point b set "Text"=a.text
from
(
  select
    a.gid as gid,
    b."Text" as text
  from
    trash.arqueta_point a inner join
    trash.arqueta_point b on
    a."MSLink"=b."MSLink"
  where
    a."Text" is null and b."Text" is not null
) a
where b.gid=a.gid;

/**
 *
 * Example 1.
 *
 */
with variable_keys as (
  select
    array_agg(variable_key)::text[] as keys
  from
    cell_meta.variable
  where gridder_task_id = 'gridderTaskDiscretePolyAreaSummaryMunicipio'
)
update cell_data.data
set data = a.data - b.keys
from
  cell_data.data a, variable_keys b
where
  b.keys is not null;
  
/**

  Example 2.
  
*/
with cells as (
  select zoom, x, y
  from cell__getcellsbyvarkeys(
    ARRAY[cell__getvariablekeysbygriddertaskid(:'griddertaskid')], false, :zoom, null)
)
update cell_data.data a
set data = a.data || '{ "1111": null }'::jsonb
from cells b
where a.zoom = b.zoom and a.x = b.x and a.y = b.y;