Re: Update two tables returning id from insert CTE Query

Поиск
Список
Период
Сортировка
От Patrick B
Тема Re: Update two tables returning id from insert CTE Query
Дата
Msg-id CAJNY3itPSMM6jatLMME-xueFisvMG=2POQUmp60-nW_djCkvPA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Update two tables returning id from insert CTE Query  (Kevin Grittner <kgrittn@gmail.com>)
Ответы Re: Update two tables returning id from insert CTE Query  (Kevin Grittner <kgrittn@gmail.com>)
Список pgsql-general
Дерево обсуждения


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

В списке pgsql-general по дате отправления:

Предыдущее
От: Kevin Grittner
Дата:
Сообщение: Re: Update two tables returning id from insert CTE Query
Следующее
От: "Mike Sofen"
Дата:
Сообщение: how to monitor the progress of really large bulk operations?