Re: [GENERAL] Update two tables returning id from insert CTE Query

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Patrick B
Sent: Tuesday, September 27, 2016 4:00 PM
To: David G. Johnston <david.g.johns...@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.johns...@gmail.com<mailto:david.g.johns...@gmail.com>>:
On Mon, Sep 26, 2016 at 9:06 PM, Patrick B 
<patrickbake...@gmail.com<mailto:patrickbake...@gmail.com>> wrote:

I'm doing this now:


sel AS (
SELECT i.id<http://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