Обсуждение: Update two tables returning id from insert CTE Query
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
);
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
)
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)
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.2ThanksPatrick
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;
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
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
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
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
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 expressionAnd 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.
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
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.
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
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
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
);
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
);
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
);
WITH rows AS (
SELECT
t1.clientid,
t1.name_first,
t1.name_last
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
)
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
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.
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
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)
);
Actually I can't use name_last or name_first because some of the rows have name_last/name_first = nullI'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 rowsc_Id is the column I need to update from the inserted on table_1So.. not many options here