Single query to replace a value in multiple tables with same column

Question

I am using Postgres SQL. I am having multiple tables say table1, table2, table3, table4, ... with a common column say name in each of the tables. I want to replace a the value 'Jhon' with 'Jimmy' in every name column with a single query.

Presently I am using below query:

update table1 set "name" = 'Jimmy' where "name" = 'Jhon';

But I have to run one by one for every table in database. Please suggest a single query which can do the task.

Answer 1

To just generate UPDATE statements:

CREATE OR REPLACE FUNCTION f_update_all(_old_val text, _new_val text
                                      , VARIADIC _tbls regclass[])
  RETURNS SETOF text
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl regclass;
BEGIN
   FOREACH _tbl IN ARRAY _tbls
   LOOP
      RETURN NEXT 'UPDATE ' || _tbl || ' SET name = $2 WHERE name = $1;';
   END LOOP;
END
$func$;

To execute dynamically:

CREATE OR REPLACE FUNCTION f_update_all(_old_val text, _new_val text
                                      , VARIADIC _tbls regclass[])
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl regclass;
BEGIN
   FOREACH _tbl IN ARRAY _tbls
   LOOP
      EXECUTE 'UPDATE ' || _tbl || ' SET name = $2 WHERE name = $1'
      USING $1, $2;
   END LOOP;
END
$func$;

db<>fiddle here

About VARIADIC:

• Pass multiple values in single parameter

About FOREACH:

• Postgres - array for loop

About passing and concatenating table names safely:

• Table name as a PostgreSQL function parameter

And pass values safely with the USING clause.