Thread: Slick way to update multiple tables.

Slick way to update multiple tables.

From
"Theodore M Rolle, Jr."
Date:
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?

--
 GnuPG/PGP key: 0xDD4276BA
 +-----------------------------------------------------------------------------------------------------+
 | 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510          |
 |   58209 74944[59230 78164]06286 20899 86280 +----------------------------------|
 |   34825 34211 70679*82148 08651 32823 06647 |    May the spirit                |
 |   09384 46095 50582 23172 53594 08128 48111  |      of π spread                |
 |   74502 84102 70193 85211 05559 64462 29489 |    around the world.         |
 |   54930 38196 44288 10975 66593 34461 28475 |      PI VOBISCUM!          |
 |   38196 44288 10975 66593 34461 28475 64823 +---------------------------------|
 |   37867 83165 27120 19091 45648 56692 34603 48610 45432 6648...         |
 +----------------------------------------------------------------------------------------------------+

Re: Slick way to update multiple tables.

From
Michael Lewis
Date:
You can have an updatable view.

Re: Slick way to update multiple tables.

From
"Theodore M Rolle, Jr."
Date:


On Thu, Apr 1, 2021 at 12:43 PM Michael Lewis <mlewis@entrata.com> wrote:
You can have an updatable view.
 
Show-stopper?
The defining query of the view must have exactly one entry in the FROM clause, which can be a table or another updatable view.

Or multiple VIEWs? I've never done VIEWs...


--
 GnuPG/PGP key: 0xDD4276BA
 +-----------------------------------------------------------------------------------------------------+
 | 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510          |
 |   58209 74944[59230 78164]06286 20899 86280 +----------------------------------|
 |   34825 34211 70679*82148 08651 32823 06647 |    May the spirit                |
 |   09384 46095 50582 23172 53594 08128 48111  |      of π spread                |
 |   74502 84102 70193 85211 05559 64462 29489 |    around the world.         |
 |   54930 38196 44288 10975 66593 34461 28475 |      PI VOBISCUM!          |
 |   38196 44288 10975 66593 34461 28475 64823 +---------------------------------|
 |   37867 83165 27120 19091 45648 56692 34603 48610 45432 6648...         |
 +----------------------------------------------------------------------------------------------------+

Re: Slick way to update multiple tables.

From
Michael Lewis
Date:
Joins are allowed though.

Re: Slick way to update multiple tables.

From
Michael Lewis
Date:
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.

Re: Slick way to update multiple tables.

From
Paul Jungwirth
Date:
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



Re: Slick way to update multiple tables.

From
Ron
Date:
On 4/1/21 11:16 AM, Theodore M Rolle, Jr. wrote:
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?


Did you join the three tables when querying?

--
Angular momentum makes the world go 'round.