Thread: Update two tables returning id from insert CTE Query

Update two tables returning id from insert CTE Query

From

Patrick B

Date:

27 September 2016, 06:23:37

Hi guys,

I've got 2k rows in a table:

CREATE TABLE
public.not_monthly
(
id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
clientid BIGINT,
name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
);

I want to insert data from public.not_monthly to public.table_1(clientid,name_first) and public.table_2(client_id,c_id(FROM table_1),name_last)

table_2.c_id must have the ID from the insert on the table_1 table.

I did this:

WITH rows AS (
SELECT
t1.id,
t1.clientid,
t1.name_first,
t1.name_last
row_number() OVER (ORDER BY t1.id) AS rn
FROM
public.not_monthly t1
),
ins_table_1 AS (
INSERT INTO public.table_1 (clientid,name_first)
SELECT
clientid,
name_first
FROM rows
RETURNING id
),
ins_table_2 AS (
INSERT INTO public.table_2 (name_last,clientid)
SELECT
name_last,
clientid
FROM rows
RETURNING id
)

Then, I was able to select the table_1.id using:

SELECT i.id AS table_1_id, s.id AS not_monthly_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)

So I'd imagine now I would do the update? How can I update table_2.c_id with the ins_table_1.id value?

I'm using Postgres 9.2

Thanks

Patrick

Re: Update two tables returning id from insert CTE Query

From

Patrick B

Date:

27 September 2016, 07:07:38

2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:

Hi guys,

I've got 2k rows in a table:
CREATE TABLE
public.not_monthly
(
id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
clientid BIGINT,
name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
);

I want to insert data from public.not_monthly to public.table_1(clientid,name_first) and public.table_2(client_id,c_id(FROM table_1),name_last)

table_2.c_id must have the ID from the insert on the table_1 table.

I did this:
WITH rows AS (
SELECT
t1.id,
t1.clientid,
t1.name_first,
t1.name_last
row_number() OVER (ORDER BY t1.id) AS rn
FROM
public.not_monthly t1
),
ins_table_1 AS (
INSERT INTO public.table_1 (clientid,name_first)
SELECT
clientid,
name_first
FROM rows
RETURNING id
),
ins_table_2 AS (
INSERT INTO public.table_2 (name_last,clientid)
SELECT
name_last,
clientid
FROM rows
RETURNING id
)

Then, I was able to select the table_1.id using:
SELECT i.id AS table_1_id, s.id AS not_monthly_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)

So I'd imagine now I would do the update? How can I update table_2.c_id with the ins_table_1.id value?
I'm using Postgres 9.2

Thanks
Patrick

I'm doing this now:

sel AS (
SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get ERROR: more than one row returned by a subquery used as an expression

Re: Update two tables returning id from insert CTE Query

From

Vitaly Burovoy

Date:

27 September 2016, 14:24:41

On 9/26/16, Patrick B <patrickbakerbr@gmail.com> wrote:
> Hi guys,
>
> I've got 2k rows in a table:

...

> So I'd imagine now I would do the update? How can I update table_2.c_id
> with the ins_table_1.id value?
> I'm using Postgres 9.2
>
> Thanks
> Patrick
>

Hello,

It is not possible to change one row more than once by one query.
You try to do so by inserting in ins_table_2 (it is "change" of a row)
and update the inserted row by a final query.

It is hard to understand what you want to do because your query is
very artificial: get non-unique data from a table, split it (to get
less unique data) but then "match" two non-unique data via inserted
unique identifier.

The only common hint I can give you is to use data from ins_table_1 in
SELECT part of the ins_table_2 (which should be now not CTE, but the
final query):
WITH rows AS (
SELECT ...
),
ins_table_1 AS (
INSERT INTO public.table_1 (clientid,name_first)
...
RETURNING id -- may be also clientid and name_first?
)
INSERT INTO public.table_2 (c_id, name_last,clientid)
SELECT
        ins.id,
        rows.name_last,
        rows.clientid
FROM rows
INNER JOIN (
    SELECT ..., ... OVER()... FROM ins_table_1
) ins ON (...)

Note than CTEs not have indexes and a join process is not fast (for
bigger number of rows).

--
Best regards,
Vitaly Burovoy

Re: Update two tables returning id from insert CTE Query

From

Vitaly Burovoy

Date:

27 September 2016, 14:29:40

On 9/26/16, Patrick B <patrickbakerbr@gmail.com> wrote:
> 2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:
> I'm doing this now:
>
> sel AS (
>> SELECT i.id AS c_id
>> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1)
>> i
>> JOIN rows s USING (rn)
>> )
>> UPDATE table_2 SET c_id =
>> (
>>   SELECT c_id
>>   FROM sel
>>   ORDER BY c_id
>> )
>> WHERE clientid = 124312;
>
>
> But I get *ERROR:  more than one row returned by a subquery used as an
> expression*
>

To update rows of one table by rows from another table you should use
UPDATE ... SET ... FROM ... WHERE ...
clause described in the docs[1] (see example around the sentence "A
similar result could be accomplished with a join:" and note below).

[1] https://www.postgresql.org/docs/devel/static/sql-update.html
--
Best regards,
Vitaly Burovoy

Re: Update two tables returning id from insert CTE Query

From

Patrick B

Date:

27 September 2016, 22:10:15

2016-09-28 0:29 GMT+13:00 Vitaly Burovoy <vitaly.burovoy@gmail.com>:

On 9/26/16, Patrick B <patrickbakerbr@gmail.com> wrote:
> 2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:
> I'm doing this now:
>
> sel AS (
>> SELECT i.id AS c_id
>> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1)
>> i
>> JOIN rows s USING (rn)
>> )
>> UPDATE table_2 SET c_id =
>> (
>> SELECT c_id
>> FROM sel
>> ORDER BY c_id
>> )
>> WHERE clientid = 124312;
>
>
> But I get *ERROR: more than one row returned by a subquery used as an
> expression*
>

To update rows of one table by rows from another table you should use
UPDATE ... SET ... FROM ... WHERE ...
clause described in the docs[1] (see example around the sentence "A
similar result could be accomplished with a join:" and note below).

[1] https://www.postgresql.org/docs/devel/static/sql-update.html
--
Best regards,
Vitaly Burovoy

clientid is the same for all the rows....

Your example doesn't work. And isn't much different than mine.

Patrick

Re: Update two tables returning id from insert CTE Query

From

"David G. Johnston"

Date:

27 September 2016, 22:54:20

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

I'm doing this now:

sel AS (
SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get ERROR: more than one row returned by a subquery used as an expression

And this surprises you why?

I'd advise you get whatever it is you are trying to accomplish working using multiple queries in a transaction, probably with the help of temporary tables, then post that self-contained working example and ask for suggestions on how to turn it into a single query using CTEs (if its ever worth the effort at that point).

David J.

Re: Update two tables returning id from insert CTE Query

From

Patrick B

Date:

27 September 2016, 23:00:29

2016-09-28 8:54 GMT+13:00 David G. Johnston <david.g.johnston@gmail.com>:

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

I'm doing this now:

sel AS (
SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get ERROR: more than one row returned by a subquery used as an expression

And this surprises you why?

I'd advise you get whatever it is you are trying to accomplish working using multiple queries in a transaction, probably with the help of temporary tables, then post that self-contained working example and ask for suggestions on how to turn it into a single query using CTEs (if its ever worth the effort at that point).

David J.

isn't clear what I'm trying to achieve? That's what I need, I just want a way to do that, as the way I'm doing isn't working.

Re: Update two tables returning id from insert CTE Query

From

Igor Neyman

Date:

27 September 2016, 23:10:19

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Patrick B
Sent: Tuesday, September 27, 2016 4:00 PM
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 8:54 GMT+13:00 David G. Johnston <david.g.johnston@gmail.com>:

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

I'm doing this now:

sel AS (
SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get ERROR: more than one row returned by a subquery used as an expression

And this surprises you why?

I'd advise you get whatever it is you are trying to accomplish working using multiple queries in a transaction, probably with the help of temporary tables, then post that self-contained working example and ask for suggestions on how to turn it into a single query using CTEs (if its ever worth the effort at that point).

David J.

isn't clear what I'm trying to achieve? That's what I need, I just want a way to do that, as the way I'm doing isn't working.

Patrick,

You need to explain your problems in more “coherent” way, David suggested one.

If you aren’t willing, people will stop responding to your request, they are not obligated to read your mind.

Regards,

Igor Neyman

Re: Update two tables returning id from insert CTE Query

From

"David G. Johnston"

Date:

27 September 2016, 23:20:02

On Tue, Sep 27, 2016 at 1:10 PM, Igor Neyman <ineyman@perceptron.com> wrote:

Patrick,

You need to explain your problems in more “coherent” way, David suggested one.
If you aren’t willing, people will stop responding to your request, they are not obligated to read your mind.

I'll put it this way - the only conclusion I can draw in reading the provided code is that the data model is screwed up and may not be capable of accommodating this goal. Albeit this is based upon a incompletely specified model...

If you want table1 and table2 to have a 1-to-1 relationship then the assignment of the unique key should not occur via next_val() calls in the default expression of either table. When you query "not_monthly" you need to figure out what the new ID should be and explicitly add it to both INSERT statements.

David J.

Re: Update two tables returning id from insert CTE Query

From

Kevin Grittner

Date:

27 September 2016, 23:23:15

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

Re: Update two tables returning id from insert CTE Query

From

Patrick B

Date:

27 September 2016, 23:34:24

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

Re: Update two tables returning id from insert CTE Query

From

Kevin Grittner

Date:

28 September 2016, 00:11:52

On Tue, Sep 27, 2016 at 3:33 PM, Patrick B <patrickbakerbr@gmail.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.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Update two tables returning id from insert CTE Query

From

Patrick B

Date:

28 September 2016, 00:26:05

2016-09-28 10:11 GMT+13:00 Kevin Grittner <kgrittn@gmail.com>:

On Tue, Sep 27, 2016 at 3:33 PM, Patrick B <patrickbakerbr@gmail.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

Re: Update two tables returning id from insert CTE Query

From

Patrick B

Date:

28 September 2016, 00:32:27

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

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

Re: Update two tables returning id from insert CTE Query

From

"David G. Johnston"

Date:

28 September 2016, 00:38:16

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

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.