Search Postgresql Archives

Re: Update two tables returning id from insert CTE Query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 9/26/16, Patrick B <patrickbakerbr@xxxxxxxxx> wrote:
> 2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr@xxxxxxxxx>:
> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux