mySQL update/subquery/group by fun

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

mySQL update/subquery/group by fun

Post by d3ad1ysp0rk »

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?
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

Is this impossible? Should I just script it?

I'd rather not.. but the experts can't help, I guess that's my only choice!
Post Reply