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