MySQL: Difficulty in Updating Rows with JOIN and Subqueries in a Single Statement
I'm working on a project and hit a roadblock... I'm stuck on something that should probably be simple. I'm having trouble updating rows in a MySQL 8.0 database using a JOIN and subqueries in a single statement. The goal is to update the `salary` of employees based on the average salary of their respective departments. I'm trying to do this in such a way that only employees earning less than their department's average get updated. However, I keep receiving a `#1242 - Subquery returns more than 1 row` behavior. Here's the query I've written: ```sql UPDATE employees AS e JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) AS dept_avg ON e.department_id = dept_avg.department_id SET e.salary = dept_avg.avg_salary WHERE e.salary < dept_avg.avg_salary; ``` Unfortunately, this results in the behavior I mentioned. I've also tried breaking it down into multiple steps by first selecting the average salaries into a temporary table and then performing the update, but it feels inefficient. As a workaround, I converted the average calculation into a derived table, but it still gives me issues. I checked the data to ensure that no department has fewer than two employees, so there should not be any edge cases causing the multiple row return. What am I missing here? Is there a better way to structure this update query in MySQL to avoid the subquery scenario while still achieving the desired outcome? This is part of a larger CLI tool I'm building. Is there a better approach? My development environment is Ubuntu.