From e3f156e8625f661b09f950eaad3d4cfcc3b1e5d9 Mon Sep 17 00:00:00 2001
From: Hugues Hiegel <hugues@hiegel.fr>
Date: Thu, 11 Dec 2008 14:00:53 +0100
Subject: [DB] mysql db cleanup script

---
 database.cleanup | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)
 create mode 100644 database.cleanup

diff --git a/database.cleanup b/database.cleanup
new file mode 100644
index 0000000..a3ebe2b
--- /dev/null
+++ b/database.cleanup
@@ -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 ) ;
+DELETE badges FROM badges, tmp WHERE badges.username = tmp.username AND tmp.count > 1 ;
+DROP TABLE tmp ;
+
+
-- 
cgit v1.2.3