diff options
author | Hugues Hiegel <hugues@hiegel.fr> | 2008-12-11 14:12:11 +0100 |
---|---|---|
committer | Hugues Hiegel <hugues@hiegel.fr> | 2008-12-11 14:12:11 +0100 |
commit | 44e46e5c71af611784143a2ebf1a36042c3302ac (patch) | |
tree | d62421b98ae93990763a747cf67d9433c6e0cfb1 /remove-badges-variants.mysql | |
parent | ae5ee7a18295b0f50ce2a363d01eed8a45fd9bb5 (diff) |
[DB] remove badges variants : script corrections + renaming
Diffstat (limited to 'remove-badges-variants.mysql')
-rw-r--r-- | remove-badges-variants.mysql | 16 |
1 files changed, 16 insertions, 0 deletions
diff --git a/remove-badges-variants.mysql b/remove-badges-variants.mysql new file mode 100644 index 0000000..8108fad --- /dev/null +++ b/remove-badges-variants.mysql @@ -0,0 +1,16 @@ + +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 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 ; +DROP TABLE tmp ; + |