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