2016-09-28 9:23 GMT+13:00 Kevin Grittner <kgri...@gmail.com>:
> On Tue, Sep 27, 2016 at 2:59 PM, Patrick B <patrickbake...@gmail.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
