Re: Updating 3-table dataset

On Fri, Aug 9, 2019 at 2:29 PM Moreno Andreo <moreno.andreo@evolu-s.it> wrote:
>
> Hi all,
>      I don't know if that's the heat burning my brain but I can't find a
> solution to what seemed a simple operation to me.
>
> I have 3 tables
> create table t_all
> {
> id uuid,
> ref_id uuid     (FK to t_ana.id)
> };
> create table t_ana
> {
> id uuid,
> code text
> };
> create table t_app
> {
> id uuid,
> code text    (subset of t_ana.code)
> }
> I need to update t_all set t_all.id = t_app.id having t_ana.code in
> t_app.code (I wrote it in some kind of meta-sql but I hope it's clear)
> I tried to create a view but I need an INSTEAD OF trigger, since it
> spreads among 3 tables so I hope there's some faster path to achieve the
> solution....
>

Not sure I got what you need, and I've not tested, but something like
the following:

WITH must_update AS (
SELECT app.id AS app_id, ana.id AS ana_id
FROM t_app app, t_ana ana
WHERE app.code = ana.code
)

UPDATE t_all
SET id = ( SELECT app_id FROM must_update WHERE ref_id = must_update.ana_id );

I've written the CTE because it is a little clearer in my mind, but
you can push down as a subquery of course.

Luca