Re: Update two tables returning id from insert CTE Query

On 9/26/16, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:
> Hi guys,
>
> I've got 2k rows in a table:

...

> So I'd imagine now I would do the update? How can I update table_2.c_id
> with the ins_table_1.id value?
> I'm using Postgres 9.2
>
> Thanks
> Patrick
>

Hello,

It is not possible to change one row more than once by one query.
You try to do so by inserting in ins_table_2 (it is "change" of a row)
and update the inserted row by a final query.

It is hard to understand what you want to do because your query is
very artificial: get non-unique data from a table, split it (to get
less unique data) but then "match" two non-unique data via inserted
unique identifier.

The only common hint I can give you is to use data from ins_table_1 in
SELECT part of the ins_table_2 (which should be now not CTE, but the
final query):
WITH rows AS (
SELECT ...
),
ins_table_1 AS (
INSERT INTO public.table_1 (clientid,name_first)
...
RETURNING id -- may be also clientid and name_first?
)
INSERT INTO public.table_2 (c_id, name_last,clientid)
SELECT
ins.id,
rows.name_last,
rows.clientid
FROM rows
INNER JOIN (
SELECT ..., ... OVER()... FROM ins_table_1
) ins ON (...)

Note than CTEs not have indexes and a join process is not fast (for
bigger number of rows).

--
Best regards,
Vitaly Burovoy