Wikipedia:People by year/Reports/Multiple cats/SQL

From Wikipedia, the free encyclopedia
Jump to navigation Jump to search

DROP TABLE IF EXISTS temp_botcleancats;

CREATE TABLE temp_botcleancats
SELECT p_id, p_title, cur_text, 0 AS oc
FROM temp_peopleyr, cur    # see [[Wikipedia:People by year]]
WHERE p_id=cur_id
LIMIT 100000;

ALTER TABLE temp_botcleancats ADD KEY (p_id);

Update temp_botcleancats
SET oc=1
WHERE cur_text LIKE '%[[Category:_____births%'
AND (LENGTH(cur_text) - LENGTH(REPLACE(REPLACE(cur_text, 'births]', ''), 'births|', '')))/7 >1;

Update temp_botcleancats
SET oc=1
WHERE cur_text LIKE '%[[Category:_____deaths%'
AND (LENGTH(cur_text) - LENGTH(REPLACE(REPLACE(cur_text, 'deaths]', ''), 'deaths|', '')))/7 >1;

SELECT CONCAT('#[[', p_title, ']]')
INTO OUTFILE 'wp_multiplecats.txt'
FROM temp_botcleancats
WHERE oc <> 0