Delete duplicates and reroute referencing rows to new master
Can be a single statement with data-modifying CTEs:

WITH blacklist AS (
   SELECT *
   FROM  (
      SELECT id
           , first_value(id) OVER (PARTITION BY user_id, name
                                   ORDER BY j_ct DESC NULLS LAST, id) AS master_id
                                                               -- id as tiebreaker
      FROM   purposes
      LEFT   JOIN (  -- left join to preserve rows without any journeys
         SELECT purpose_id AS id, count(*) AS j_ct
         FROM   journeys
         GROUP  BY 1
         ) j USING (id)
      ) sub
   WHERE  master_id &lt;&gt; id  -- is not master_id
   )
 , del AS (
   DELETE FROM purposes p
   USING  blacklist b
   WHERE  b.id = p.id
   )
UPDATE journeys j
SET    purpose_id = b.master_id
FROM   blacklist b
WHERE  b.id = j.purpose_id;


This should do exactly what you need - even in cases of ties or where no journeys exist at all.

Step by step

The 1st CTE blacklist identifies all IDs in table purposes where another row exists with the same (user_id, name) but more associated journeys. Remember that other ID as master_id to reassign journeys further down the road. id serves as tiebreaker: the smallest id prevails.

The 2nd CTE del deletes the identified dupes.
The outer UPDATE reroutes journeys to their new master.

Note DESC NULLS LAST in the ORDER BY clause. Important for cases with no journeys at all, since that result in a NULL value for the journey count j_ct. See:


PostgreSQL sort by datetime asc, null first?


Use the window function first_value() to get the master_id for each group of peers - after forming groups with PARTITION BY and sorting properly with ORDER BY.