From: | Luca Ferrari <fluca1978(at)gmail(dot)com> |
---|---|
To: | Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it> |
Cc: | PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Updating 3-table dataset |
Date: | 2019-08-09 14:50:05 |
Message-ID: | CAKoxK+7p4fEtOfq6+cssKYZV2985v6Lrc9sh84HPtu5hGEeC3w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
Luca
From | Date | Subject | |
---|---|---|---|
Next Message | Benedict Holland | 2019-08-09 14:57:59 | Re: Understanding PostgreSQL installer debug log |
Previous Message | Adrian Klaver | 2019-08-09 14:45:38 | Re: Understanding PostgreSQL installer debug log |