Re: Update two tables returning id from insert CTE Query

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Patrick B
Sent: Tuesday, September 27, 2016 4:00 PM
To: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)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(dot)g(dot)johnston(at)gmail(dot)com<mailto:david(dot)g(dot)johnston(at)gmail(dot)com>>:
On Mon, Sep 26, 2016 at 9:06 PM, Patrick B <patrickbakerbr(at)gmail(dot)com<mailto:patrickbakerbr(at)gmail(dot)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