Re: Update two tables returning id from insert CTE Query

2016-09-28 9:23 GMT+13:00 Kevin Grittner <kgrittn(at)gmail(dot)com>:

> On Tue, Sep 27, 2016 at 2:59 PM, Patrick B <patrickbakerbr(at)gmail(dot)com>
> wrote:
>
> [sel is a relation which can have multiple rows; the fact that it
> is being generated in a CTE isn't relevant for purposes of the
> error.]
>
> >>>> UPDATE table_2 SET c_id =
> >>>> (
> >>>> SELECT c_id
> >>>> FROM sel
> >>>> ORDER BY c_id
> >>>> )
> >>>> WHERE clientid = 124312;
>
> >>>> ERROR: more than one row returned by a subquery used as an expression
>
> > isn't clear what I'm trying to achieve?
>
> Nope.
>
> > That's what I need, I just want a way to do that, as the way I'm
> > doing isn't working.
>
> You are specifying that you want to assign all the "c_id" values
> from the "sel" relation to the "c_id" column in "table2" for any
> and all rows which have a "clientid" value of 124312. Effectively
> the database is complaining that it can only store one value, not a
> set of values. I can only guess at what you might be intending to
> ask the database to do. Can you explain what you are trying to do?
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Sorry.. didn't mean to be rude... just in my thoughts I've already
explained it well.
Let see if the more clear now:

Table has data from a CSV file"
>
> CREATE TABLE
> public.not_monthly
> (
> id BIGINT DEFAULT "nextval"('"not_monthly_id_seq"'::"regclass") NOT
> NULL,

clientid BIGINT,
> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> );

Target table number 1 - On this table, I'll get clientid and name_first
from public.not_monthly:

> CREATE TABLE
> table_1
> (
> id BIGINT DEFAULT "nextval"('"table_1_id_seq"'::"regclass") NOT
> NULL,
> clientid BIGINT DEFAULT 0 NOT NULL,
> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> );

Target table number 2 - On this table, I'll get clientid and name_last
from public.not_monthly
and c_id from table_1.id:

> CREATE TABLE
> table_2
> (
> id BIGINT DEFAULT "nextval"('"table_2_id_seq"'::"regclass") NOT
> NULL,
> c_id BIGINT, --This value must be taken from table_1 inserted sql
> clientid BIGINT DEFAULT 0 NOT NULL,
> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> );

So, it would be:

*1 - Select the data*

> WITH rows AS (
> SELECT
> t1.clientid,
> t1.name_first,
> t1.name_last
> FROM
> public.not_monthly t1
> ),

*2 - Insert the data into table_1(clientid,name_first)*

> ins_table_1 AS (
> INSERT INTO public.table_1 (clientid,name_first)
> SELECT
> clientid,
> name_first
> FROM rows
> RETURNING id
> ),

*3 - Insert the data into table_2(clientid,name_last)*

> ins_table_2 AS (
> INSERT INTO public.table_2 (name_last,clientid)
> SELECT
> name_last,
> clientid
> FROM rows
> RETURNING id
> )

*4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into
table_2.c_id* - This is the problem.. how can I get the inserted id from
STEP2 and put it into c_id respecting the order?

clientid is the same for all the rows.. so I can't put a WHERE using
clientid because it won't work.

Patrick