Update Returning Order by in postgresql

Question

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

Currently I have

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

This works fine. But what if I want to return the rows affected in a specified order. Something like

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

This does not work. Anyone got a good suggestion?

Answer 1

This can be done using a data modifying CTE (common table expression):

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