MySQL UPDATE with JOIN failing when modifying the same foreign key used in JOIN condition

Other Questions MySQL
1

I’m dealing with two tables in MySQL and facing issues with an UPDATE command that involves a JOIN.

The Employee table comprises the following columns:

  • EmpId (primary key)
  • EmpName
  • EmpDeptId (foreign key)

Meanwhile, the Departments table includes:

  • DeptId (primary key)
  • DeptName

The Departments table has two entries: DeptId 1 representing “Marketing” and DeptId 2 for “Sales”.

There’s an employee named Bob, who is in Marketing (DeptId = 1). I want to change his department to Sales, but I only have the department name “Sales” and not the ID 2.

The following straightforward update works without issues:

UPDATE Employee 
SET EmpDeptId = 2 
WHERE EmpName = 'Bob'

However, I need to perform this operation using the department name instead of its ID. I attempted this query but it hasn’t succeeded:

UPDATE Employee AS E
INNER JOIN Departments AS D ON D.DeptId = E.EmpDeptId
SET E.EmpDeptId = D.DeptId
WHERE D.DeptName = 'Sales' AND E.EmpName = 'Bob'

I’ve experimented with various methods using aliases and different JOIN configurations, but nothing appears to be right. Can anyone help me understand what the problem might be?

2

yeah, you’re joining on bob’s current dept (marketing) but filtering for sales - they don’t match so nothing gets updated. just grab the sales dept id directly: update employee set empdeptid = (select deptid from departments where deptname = 'sales') where empname = 'bob' - way cleaner than messing with joins.

3

Your query’s broken because you’re joining on Marketing (Bob’s current department) but trying to set the department ID to that same value. Since Bob’s in Marketing (DeptId = 1), the JOIN grabs the Marketing record, so D.DeptId will always be 1, not 2. You need a subquery to grab the Sales department ID separately: sql UPDATE Employee SET EmpDeptId = (SELECT DeptId FROM Departments WHERE DeptName = 'Sales') WHERE EmpName = 'Bob' This ditches the problematic JOIN and directly gets the target department ID. I’ve hit this exact issue building an HR system - the subquery approach is way more reliable and clearer than trying to force JOINs to work for updates like this.