This is a postgresql DB. I have users in my users table that are associated with a customer entity by a customer_id column. The customer is broken down into smaller segments by department and the users are assigned to a department. It looks something like this.

Customer Table
id, name, external_id, etc…

Department Table
id, customer_id, name, etc…

User Table
id, customer_id, department_id, etc…

I want to create new customer records for each department and migrate the users to the correct new customer record based on their current department affiliation. I have already created the new customer records and tracked their previous department_id in the external_id field just so I have that for reference.

I now need to update each user’s customer_id to map them to the correct new customer record. I tried to match the user’s current department_id to the corresponding external_id in the customer table using the SQL below, but that didn’t work. It didn’t throw an error, but also didn’t make the change I needed.

update users u set customer_id = c.id from customers c where cast(u.department_id as varchar)=c.external_id and c.id=242

Anyone have a strategy that would help me avoid dozens of manual queries to update these records?