diff options
Diffstat (limited to 'remove-badges-variants.mysql')
| -rw-r--r-- | remove-badges-variants.mysql | 21 |
1 files changed, 18 insertions, 3 deletions
diff --git a/remove-badges-variants.mysql b/remove-badges-variants.mysql index 69cc068..3f29d2a 100644 --- a/remove-badges-variants.mysql +++ b/remove-badges-variants.mysql @@ -10,8 +10,23 @@ USE lastfm DROP TABLE IF EXISTS tmp ; CREATE TEMPORARY TABLE tmp (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ; -#SELECT badges.username, badges.type, badges.style, badges.color FROM badges, tmp WHERE badges.username = tmp.username AND badges.type = tmp.type AND tmp.count > 1 ; -SELECT badges.png FROM badges, tmp WHERE badges.username = tmp.username AND badges.type = tmp.type AND tmp.count > 1 ; -DELETE badges FROM badges, tmp WHERE badges.username = tmp.username AND badges.type = tmp.type AND tmp.count > 1 ; + +SELECT badges.png + FROM badges, tmp + WHERE badges.username = tmp.username + AND ( (badges.type = tmp.type AND tmp.count > 1) + OR ( (badges.type LIKE concat(tmp.type,"%") OR tmp.type LIKE concat(badges.type,"%")) + AND badges.type != tmp.type ) + ) ; + +DELETE badges.* + FROM badges, tmp + WHERE badges.username = tmp.username + AND ( (badges.type = tmp.type AND tmp.count > 1) + OR ( (badges.type LIKE concat(tmp.type,"%") OR tmp.type LIKE concat(badges.type,"%")) + AND badges.type != tmp.type ) + ) ; + + DROP TABLE tmp ; |