-1

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.

  • Is the issue having to commit all of the updates to all tables at once, or is the issue needing to update a lot of values (more than just Jhon -> Jimmy) on those tables? – bbaird Jul 30 '20 at 20:44
  • Why the mysql tag if you are using Postgres? – a_horse_with_no_name Jul 30 '20 at 21:19
  • Why do you have so many tables with the same structure and information? That sounds like a terrible design. – a_horse_with_no_name Jul 30 '20 at 21:20
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:

About FOREACH:

About passing and concatenating table names safely:

And pass values safely with the USING clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.