Perform query on multiple tables

Question

I need to apply the following query to 60+ tables:

UPDATE variable_table_name ab
SET    elem_nr = ef.elem_nr
FROM   ga_mit_elemnr ef
WHERE  (ab.elem_nr NOT IN (
       SELECT elem_nr FROM strassenelemente cd) OR 
       ab.elem_nr IS NULL) AND ST_WITHIN(ab.geom, ef.geom)

Is there a way to automate this in PostgreSQL?

Answer 1

You can write a small Postgresql loop to iterate over all the tables you want.

With this query, you get all tables.

SELECT table_name, table_schema FROM information_schema.tables

Probably you don't want all tables. In the loop below, you can uncomment the WHERE clause just to select the tables you want.

The loop looks like this:

DO $do$
DECLARE
    sch text; tbl text; tblfullname text;
BEGIN
    sch := 'public';
    FOR tbl, sch IN SELECT table_name, table_schema FROM information_schema.tables -- WHERE table_schema = sch and table_name ilike '%mytables%'
    LOOP
        tblfullname := format($$%s.%s$$, sch, tbl);
        raise notice 'UPDATE TABLE: %', format($$ UPDATE %s ab set elem_nr = ef.elem_nr FROM   ga_mit_elemnr ef WHERE  (ab.elem_nr NOT IN (SELECT elem_nr FROM strassenelemente cd) OR ab.elem_nr IS NULL) AND ST_WITHIN(ab.geom, ef.geom); $$, tblfullname );
        EXECUTE format($$ UPDATE %s ab SET elem_nr = ef.elem_nr FROM   ga_mit_elemnr ef WHERE  (ab.elem_nr NOT IN (SELECT elem_nr FROM strassenelemente cd) OR ab.elem_nr IS NULL) AND ST_WITHIN(ab.geom, ef.geom); $$, tblfullname );
    END LOOP;
END;
$do$;

The raise notice is just for debugging.

Answer 2

How about something like this..?

DECLARE @sql NVARCHAR(max)

SELECT @sql = N' '+ String_agg(Concat(N'
UPDATE ', CONVERT(NVARCHAR(max), Quotename(col_table_name)), N'
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];'), N'' + CHAR(13) +  CHAR(13))  
FROM   information_schema.tables
WHERE  [condition]

EXEC (@sql) 

Once you get the query, you can check the query by using the SELECT statement.

SELECT (@sql)

If the query is okay, then you can execute it.

EXEC (@sql) 

Example: https://data.stackexchange.com/stackoverflow/query/1011376/