Re: Update two tables returning id from insert CTE Query

2016-09-28 10:25 GMT+13:00 Patrick B <patrickbakerbr(at)gmail(dot)com>:

>
>
> 2016-09-28 10:11 GMT+13:00 Kevin Grittner <kgrittn(at)gmail(dot)com>:
>
>> On Tue, Sep 27, 2016 at 3:33 PM, Patrick B <patrickbakerbr(at)gmail(dot)com>
>> wrote:
>>
>> > 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?
>>
>> For DML you need to think of the data as being unordered sets, not
>> ordered lists. The whole concept of a relational database is that
>> related rows can be associated through their common data values.
>> You are splitting them apart and then trying to match them up again
>> to link them back together. You will be better off if you can
>> leave the relationship intact all the way through -- perhaps by
>> adding name_last to table_1.
>>
>
> Can you give me examples please?
> Patrick
>
>
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