title: Do multiple UPDATE using CTE
body: <p>I'm trying to use a CTE to do two update statements in Postgres and I'm not sure if there is a better approach to what I'm trying to do.</p>
  <pre><code>-- This script will update user records where the duplicate record value will overwrite the old value
  WITH
  update_users_Cte
  AS
  (
      SELECT 
          new.id new_user_id, 
          old.id old_user_id, 
          new.employee_id new_employee_id, 
          old.employee_id old_employee_id,
          new.display_name new_display_name, 
          old.display_name old_display_name,
          new.first_name new_first_name, 
          old.first_name old_first_name,
          new.last_name new_last_name , 
          old.last_name old_last_name,
          new.phone_number new_phone_number, 
          old.phone_number old_phone_number,
          new.job_type new_job_type, 
          old.job_type old_job_type,
          new.department_id new_department_id, 
          old.department_id old_department_id
      FROM users old
          JOIN users new ON (CONCAT(0, new.employee_id) = old.employee_id)
      WHERE LENGTH(new.employee_id) = 5 AND
          new.display_name != old.display_name OR
          new.first_name != old.first_name OR
          new.last_name != old.last_name OR
          new.phone_number != old.phone_number OR
          new.job_type != old.job_type OR
          new.department_id != old.department_id
  )
  
  UPDATE 
      users
  SET 
      phone_number = NULL
  FROM 
      update_users_Cte
  WHERE 
      employee_id = update_users_Cte.new_employee_id
  
  UPDATE 
      users
  SET 
      display_name = update_users_Cte.new_display_name,
      first_name = update_users_Cte.new_first_name,
      last_name = update_users_Cte.new_last_name,
      phone_number = update_users_Cte.new_phone_number,
      job_type = update_users_Cte.new_job_type,
      department_id = update_users_Cte.new_department_id
  FROM 
      update_users_Cte
  WHERE 
      employee_id = update_users_Cte.old_employee_id
  </code></pre>
  <p>This is the error:</p>
  <pre><code>ERROR:  syntax error at or near &quot;UPDATE&quot;
  LINE 41: UPDATE
  </code></pre>
  <p>I would like to be able to do both UPDATEs and use the CTE as I need to check it in both cases. I'm not sure if I have to wrap the whole thing in a transaction.</p>
  <p>Any help would be appreciated.</p>
  <pre><code>new_user_id | old_user_id | new_employee_id | old_employee_id | new_display_name | old_display_name | new_first_name | old_first_name | new_last_name | old_last_name | new_phone_number | old_phone_number |   new_updated_at    |   old_updated_at    | new_job_type | old_job_type  | new_department_id | old_department_id
  -------------+-------------+-----------------+-----------------+------------------+------------------+----------------+----------------+---------------+---------------+------------------+------------------+---------------------+---------------------+--------------+---------------+-------------------+-------------------
           474 |       19710 | 35275           | 035275          |                  |                  | David          | David          | Coyle         | Coyle         | +447584208902    |                  | 2017-06-22 17:09:43 | 2021-01-27 15:14:43 |              |               |               418 |               418
         19701 |         432 | 21239           | 021239          |                  |                  | Piotr          | Piotr          | Mierniczek    | Mierniczek    |                  | +447404050330    | 2021-02-08 14:36:59 | 2017-06-22 17:09:42 |              |               |               249 |                73
         19702 |         479 | 35568           | 035568          |                  |                  | Manjita        | Manjita        | Kunwar        | Kunwar        |                  | +447847370860    | 2021-01-15 15:51:44 | 2021-01-15 15:45:20 |              |               |               317 |               317
         19707 |       19680 | 11111           | 011111          |                  | Sarika           | Sarika         | Sarika         | Sharma        | Sharma        |                  | +447700000000    | 2021-01-20 12:46:09 | 2021-01-20 12:45:12 |              | C.S. Employee |                   |
  </code></pre>