Thread: Slick way to update multiple tables.

On 4/1/21 11:54 AM, Michael Lewis wrote:
> postgresql.org/docs/current/sql-createview.html 
> <http://postgresql.org/docs/current/sql-createview.html>
> 
> My apologies. It seems INSTEAD OF triggers are required to implement 
> updates across multiple tables. I thought not if all were simple joins. 
> My mistake.

Even with INSTEAD OF triggers, if you use a view then I suppose you 
would be forced to update some of the records more often that necessary? 
(Unless your tables are 1-to-1-to-1 of course.) Or if there is some 
trick to avoid that I'd be curious to know about it.

Here is something I've done in the past:

WITH
update1(ok) AS (
   UPDATE foo SET ... WHERE ...
   RETURNING 'ok'
),
update2(ok) AS (
   UPDATE bar SET ... WHERE ...
   RETURNING 'ok'
),
update3(ok) AS (
   UPDATE baz SET ... WHERE ...
   RETURNING 'ok'
)
SELECT ok FROM update1
UNION ALL
SELECT ok FROM update2
UNION ALL
SELECT ok FROM update3
;

You could even select different messages from each update if you want to 
know how many rows you touched in each table.

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

I SELECT items from three tables.

It seems that the only way to put updated fields into their proper table is to query each field, then UPDATE each table.

Am I missing something here?

Is there a way to do this automatically?