Re: Update two tables returning id from insert CTE Query

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Update two tables returning id from insert CTE Query
Дата
Msg-id CAKFQuwap04Vyf0TjSpmOMsGRGm+=ep6AF2+1hziG-H-iEf_ARw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Update two tables returning id from insert CTE Query  (Patrick B <patrickbakerbr@gmail.com>)
Список pgsql-general
Дерево обсуждения
On Tue, Sep 27, 2016 at 2:31 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
2016-09-28 10:25 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:

Actually I can't use name_last or name_first because some of the rows have name_last/name_first = null

I'm inserting more columns that I shown:

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
company_name CHARACTER VARYING(80)
);
 

but the only value that is commun between table_1 and table_2 is the clientid and c_id.
Clientid is the same for all the rows
c_Id is the column I need to update from the inserted on table_1

So.. not many options here 

​<not tested>​

​ALTER TABLE public.not_monthly ADD COLUMN c_id bigint NULL;

UPDATE public.not_monthly SET c_id = next_val('c_id_sequence')​;  --might need a bit of futzing to make this work, but I hope you get the idea...

INSERT INTO table_1 (clientid, c_id, first_name)
SELECT client_id, c_id, first_name FROM not_monthly;

INSERT INTO table_2 (clientid, c_id, last_name)
SELECT client_id, c_id, last_name FROM not_monthly;

David J.



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

Предыдущее
От: Patrick B
Дата:
Сообщение: Re: Update two tables returning id from insert CTE Query
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [HACKERS] Re: inconsistent behaviour of set-returning functions in sub-query with random()