Re: Update two tables returning id from insert CTE Query

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B <patrickbakerbr(at)gmail(dot)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.​