CODEWITHSUNDEEP
sqlpostgresqlsql-update
iskandarblue
iskandarblue

Reputation: 7526

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

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?

Upvotes: 0

Views: 539

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

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.

Upvotes: 1

Related Questions