Thread: Updating 3-table dataset

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....

Thanks
Moreno

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.

>
> Luca
>