mySQL update/subquery/group by fun
Posted: Mon Dec 17, 2007 2:37 pm
Ugh, why can't mySQL allow me to either use the same table in a subquery on an update, or use group by on updates?
I've basically got duplicate data (don't ask me, I didn't write the code that put it there.. but it's there for more rows than we can manually fix).
Looks like this:
+--------+------+-------+
| crewid | rank | skill |
+--------+------+-------+
| 385374 | 0 | 1 |
| 385374 | 0 | 2 |
| 385374 | 0 | 3 |
| 385374 | 0 | 4 |
| 385374 | 1 | 5 |
| 385374 | 0 | 6 |
| 385374 | 0 | 7 |
| 385374 | 0 | 8 |
| 385374 | 0 | 9 |
| 385374 | 1 | 5 |
| 385374 | 1 | 4 |
| 385374 | 1 | 3 |
+--------+------+-------+
As you can see, there should be one rank per skill, so those duplicates at the end need to go. I was going to do the update to set the ranks the same (sum them) and then a delete on anything with > 1 of the same skill and crewid, limited to 1 deletion.
These were my two attempts:
UPDATE crews_upgrades t1, crews_upgrades t2 SET t1.rank = SUM(t2.rank) WHERE t1.crewid = t2.crewid AND t1.skill = t2.skill AND crewid = 385374 GROUP BY t2.skill HAVING count(t2.*) > 1;
Returns: ERROR 1111 (HY000): Invalid use of group function
UPDATE crews_upgrades t1 SET t1.rank = (SELECT SUM(t2.rank) FROM crews_upgrades t2 WHERE t2.crewid = t1.crewid AND t2.skill = t1.skill) WHERE t1.crewid = 385374;
Returns: ERROR 1093 (HY000): You can't specify target table 'crews_upgrades' for update in FROM clause
Anybody got any ideas?
I've basically got duplicate data (don't ask me, I didn't write the code that put it there.. but it's there for more rows than we can manually fix).
Looks like this:
+--------+------+-------+
| crewid | rank | skill |
+--------+------+-------+
| 385374 | 0 | 1 |
| 385374 | 0 | 2 |
| 385374 | 0 | 3 |
| 385374 | 0 | 4 |
| 385374 | 1 | 5 |
| 385374 | 0 | 6 |
| 385374 | 0 | 7 |
| 385374 | 0 | 8 |
| 385374 | 0 | 9 |
| 385374 | 1 | 5 |
| 385374 | 1 | 4 |
| 385374 | 1 | 3 |
+--------+------+-------+
As you can see, there should be one rank per skill, so those duplicates at the end need to go. I was going to do the update to set the ranks the same (sum them) and then a delete on anything with > 1 of the same skill and crewid, limited to 1 deletion.
These were my two attempts:
UPDATE crews_upgrades t1, crews_upgrades t2 SET t1.rank = SUM(t2.rank) WHERE t1.crewid = t2.crewid AND t1.skill = t2.skill AND crewid = 385374 GROUP BY t2.skill HAVING count(t2.*) > 1;
Returns: ERROR 1111 (HY000): Invalid use of group function
UPDATE crews_upgrades t1 SET t1.rank = (SELECT SUM(t2.rank) FROM crews_upgrades t2 WHERE t2.crewid = t1.crewid AND t2.skill = t1.skill) WHERE t1.crewid = 385374;
Returns: ERROR 1093 (HY000): You can't specify target table 'crews_upgrades' for update in FROM clause
Anybody got any ideas?