CODEWITHSUNDEEP
mysqlpostgresql
Aaron Ullal
Aaron Ullal

Reputation: 5245

Postgres equivalent of MySQL update based on order

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?

Upvotes: 1

Views: 41

Answers (1)

Nick
Nick

Reputation: 147216

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

Upvotes: 3

Related Questions