Re: Update two tables returning id from insert CTE Query

2016-09-28 0:29 GMT+13:00 Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com>:

> On 9/26/16, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:
> > 2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr(at)gmail(dot)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
>

clientid is the same for all the rows....
Your example doesn't work. And isn't much different than mine.

Patrick