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

Il 09/08/19 16:50, Luca Ferrari ha scritto:

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.

Thanks Luca,

that's the idea I needed... now some small trimming and I think I'll be
there

(just for the logs... the UPDATE statement needs a WHERE clause,
otherwise it will NULL the id field in all rows where ref_id is not
present in must_update :-) )

I owe you a beer :-)

Cheers

Moreno.