Re: Updating 3-table dataset

Il 09/08/19 16:50, Luca Ferrari ha scritto:
> On Fri, Aug 9, 2019 at 2:29 PM Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)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
>