Re: 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>:

> 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.​
>
>
>

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.