From c187cb0a45231b91018b318cd6494123fa4e221a Mon Sep 17 00:00:00 2001 From: Hugues Hiegel Date: Mon, 10 May 2010 17:36:26 +0200 Subject: [DataBase] little cleanup --- DataBase/cleanup.mysql | 14 ++++++++++++++ 1 file changed, 14 insertions(+) create mode 100644 DataBase/cleanup.mysql (limited to 'DataBase/cleanup.mysql') diff --git a/DataBase/cleanup.mysql b/DataBase/cleanup.mysql new file mode 100644 index 0000000..b0ad464 --- /dev/null +++ b/DataBase/cleanup.mysql @@ -0,0 +1,14 @@ +USE lastfm + +CREATE TEMPORARY TABLE users_having_a_badge (SELECT username, 0 AS has_a_badge FROM users) ; +CREATE TEMPORARY TABLE badges_having_a_user (SELECT username, 0 AS has_a_user FROM badges) ; + +UPDATE users_having_a_badge, badges SET has_a_badge = 1 WHERE users_having_a_badge.username = badges.username ; +UPDATE badges_having_a_user, users SET has_a_user = 1 WHERE badges_having_a_user.username = users.username ; + +DELETE users.* FROM users, users_having_a_badge where has_a_badge = 0 AND users.username = users_having_a_badge.username ; +SELECT badges.png FROM badges, badges_having_a_user where has_a_user = 0 AND badges.username = badges_having_a_user.username ; +DELETE badges.* FROM badges, badges_having_a_user where has_a_user = 0 AND badges.username = badges_having_a_user.username ; + +DROP TABLE users_having_a_badge; +DROP TABLE badges_having_a_user; -- cgit v1.2.3