Postgres equivalent of MySQL update based on order

Question

In MySQL it's very easy to update a table over the result of an order by, such as the following:

SET @counter=1;
UPDATE task SET priority = (@counter:=@counter+1) ORDER BY createdAt ASC;

What would be the equivalent query on PostgreSQL?

Nick · Accepted Answer · 2019-05-16 11:55:10Z

3

You can use an UPDATE with a JOIN to a table of row numbers generated with the ROW_NUMBER() function (selecting row numbers based on the order of createdAt):

UPDATE task
SET priority = p.priority
FROM (SELECT createdAt, ROW_NUMBER() OVER (ORDER BY createdAt) AS priority
      FROM task) p 
WHERE p.createdAt = task.createdAt

Demo on dbfiddle

answered May 16, 2019 at 11:55

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

Postgres equivalent of MySQL update based on order

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