Update returned order in postgresql

Question

I have a query updating rows in a table. I want the query to update rows and return the affected rows.

I currently have

UPDATE employees SET name = 'John' RETURNING employees.*;

This works great. But what if I want to return the rows touched in the order shown. Something like

UPDATE employees SET name = 'John' RETURNING employees.* ORDER BY name ASC;

This does not work. Anyone got a good offer?

+3

Kevin Joymungol 03 Sep 14 at 17:06

1 answer

a_horse_with_no_name · Accepted Answer · 2014-09-03T17:19:31+0000

This can be done using the data modifying the CTE (Common Table Expression):

with updated as (
    UPDATE employees 
        SET name = 'John' 
    RETURNING *
)
select *
from updated
ORDER BY empname ASC;