Setting a column equal to the value of a subquery in postgres

Question

I have a 4-column table in postgres called mytable. The fourth column - batch - contains only null values. My goal is to update it using the UPDATE... SET commands by setting it equal to the result of a query that uses that table (in this case NTILE(), which assigne each row to one of four equal-sized bins) . For example:

UPDATE mytable
    SET batch = (SELECT 
                    NTILE(4) OVER()
                    FROM
                    mytable)

When I try the above,the code fails with an error:

ERROR:  more than one row returned by a subquery used as an expression

The query by itself runs successfully. How would one update this column with one command in postgres?

Gordon Linoff · Accepted Answer · 2021-04-11 18:15:28Z

1

If you want to set the batch like this, then you need to use the primary key:

update mytable t
    set batch = t2.new_batch
    from (select t2.*, ntile(4) over () as new_batch
          from mytable t2
         ) t2
    where t2.pk = t.pk;

Note that the use of ntile() without order by is quite unorthodox, but that is how your question phrases the function call.

answered Apr 11, 2021 at 18:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting a column equal to the value of a subquery in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related