A common table expression that uses one ore more DML statements

with old_orders as (
   delete from orders
   where order_date <= current_date - interval '2' year
   returning *
)
insert into archived_orders
select * 
from old_orders;

Some DBMS also allow to update the result of a CTE:

with sorted as (
   select pk, 
          sort_order,
          row_number over (order by sort_order) as rn
   from some_table
)
update sorted
  set sort_order = rn;

Back to the SQL Feature Comparison