Re: Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 2:31 PM, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:

> 2016-09-28 10:25 GMT+13:00 Patrick B <patrickbakerbr(at)gmail(dot)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.