How to adapt a PostGIS query to modify the input table instead of creating a new table?

Question

I use the following SQL query to create a table that contains dissovled polygons of an existing table:

-- Schema: public
-- Table: t_union
-- Input table: input_table

CREATE TABLE public.t_union AS SELECT
    row_number() over() AS gid,
    sbqry.geom
FROM 
(SELECT
    (ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/) AS geom
FROM public.input_table
) AS sbqry;

Input polygon features:

Output polygon features:

Is there a way to modify the polygons of the input table instead of creating a new table?

Edit 1:

Meanwhile, I've created a SQL query to modify the input table:

INSERT INTO public.input_table
SELECT
    row_number() over() + 100000 AS gid,
    sbqry.geom
FROM 
(SELECT
    (ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/) AS geom
FROM public.input_table
) AS sbqry;

DELETE FROM public.input_table WHERE gid < 100000;
UPDATE public.input_table SET gid = gid - 100000;

row_number() over() + 100000 AS gid is a clumsy workaround to ignore the existing gid values and I don't know how to delete the input geometries in a simple way.

EDIT 2:

Perhaps this can be solved by using UPDATE and SET:

UPDATE public.input_table SET (geom) =
(SELECT COALESCE
    ((ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/))
FROM public.input_table);

Unfortunately, an error occurs when executing the query:

set-valued function called in context that cannot accept a set

Answer 1

You cannot remove lines in the table you are currently selecting! But you can use your solution in "Edit 1" with a temporary table:

BEGIN;

CREATE TEMP TABLE temp1 ON COMMIT DROP AS
SELECT
    row_number() over() AS gid,
    sbqry.geom AS geom
FROM 
(SELECT
    (ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/) AS geom
FROM public.input_table
) AS sbqry;

TRUNCATE public.input_table;
INSERT INTO public.input_table (gid, geom) SELECT gid, geom FROM temp1;

COMMIT;

Note: I haven't checked your part of the query for the select/union stuff...