Re: Update two tables returning id from insert CTE Query

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Tue, Sep 27, 2016 at 2:31 PM, Patrick B <patrickbakerbr@xxxxxxxxx> wrote:

2016-09-28 10:25 GMT+13:00 Patrick B <patrickbakerbr@xxxxxxxxx>:

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.