blob: 3f29d2a47f1ef35218daf386b2adb7a891457f68 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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.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 ;
|
