Re: Update two tables returning id from insert CTE Query

2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr(at)gmail(dot)com>:

> Hi guys,
>
> I've got 2k rows in a table:
>
>> CREATE TABLE
>> public.not_monthly
>> (
>> id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
>> clientid BIGINT,
>> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
>> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
>> );
>
>
> I want to insert data from public.not_monthly to
> public.table_1(clientid,name_first) and public.table_2(client_id,c_id*(FROM
> table_1)*,name_last)
>
> table_2.c_id must have the ID from the insert on the table_1 table.
>
> I did this:
>
>> WITH rows AS (
>> SELECT
>> t1.id,
>> t1.clientid,
>> t1.name_first,
>> t1.name_last
>> row_number() OVER (ORDER BY t1.id) AS rn
>> FROM
>> public.not_monthly t1
>> ),
>> ins_table_1 AS (
>> INSERT INTO public.table_1 (clientid,name_first)
>> SELECT
>> clientid,
>> name_first
>> FROM rows
>> RETURNING id
>> ),
>> ins_table_2 AS (
>> INSERT INTO public.table_2 (name_last,clientid)
>> SELECT
>> name_last,
>> clientid
>> FROM rows
>> RETURNING id
>> )
>
>
> Then, I was able to select the table_1.id using:
>
>> SELECT i.id AS table_1_id, s.id AS not_monthly_id
>> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
>> JOIN rows s USING (rn)
>
>
> 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
>

I'm doing this now:

sel AS (
> SELECT i.id AS c_id
> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
> JOIN rows s USING (rn)
> )
> UPDATE table_2 SET c_id =
> (
> SELECT c_id
> FROM sel
> ORDER BY c_id
> )
> WHERE clientid = 124312;

But I get *ERROR: more than one row returned by a subquery used as an
expression*