diff options
| author | Hugues Hiegel <hugues@hiegel.fr> | 2008-12-12 11:08:35 +0100 |
|---|---|---|
| committer | Hugues Hiegel <hugues@hiegel.fr> | 2008-12-12 11:08:35 +0100 |
| commit | c86ca9d816e66636ce729a373442cf292bf43cec (patch) | |
| tree | 4da87f3e6616ed98b1900ded2eafb30707803ab2 | |
| parent | 2e017687e570880313820005347453ce566cda3b (diff) | |
[DB] show-variants to help, and remove variants removes also #2 versions
| -rw-r--r-- | remove-badges-variants.mysql | 21 | ||||
| -rw-r--r-- | show-variants.mysql | 23 |
2 files changed, 41 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 ; diff --git a/show-variants.mysql b/show-variants.mysql new file mode 100644 index 0000000..3eeeeac --- /dev/null +++ b/show-variants.mysql @@ -0,0 +1,23 @@ + +USE lastfm + + +/** + * Deletes all entries in badges + * for each unique username + type + * where more than one style or color exists + */ + +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, badges.hits, tmp.count + 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 ; + |