This query is marked as a draft This query has been published by Matěj Suchánek.

SQL

AخA
 
USE wikidatawiki_p;
SELECT CONCAT('* [[User:',rev1.rev_user_text,'|]] [[Special:Diff/',rev1.rev_id,'|removed]] a sitelink from [[Q',epp_entity_id,']] at ',rev1.rev_timestamp,
" with comment: ''",REPLACE(REPLACE(REPLACE(rev1.rev_comment,' */','}}'),'/* wbsetsitelink-remove:','{{int:wikibase-entity-summary-wbsetsitelink-remove|'),'}} ','}}: '),"''") AS log
    FROM (SELECT * FROM wb_entity_per_page WHERE epp_redirect_target IS NULL) AS epp
    JOIN (SELECT * FROM revision WHERE rev_comment LIKE '/* wbsetsitelink-remove:%') AS rev1
        ON rev1.rev_page = epp_page_id
    LEFT JOIN (SELECT DISTINCT ips_item_id FROM wb_items_per_site) AS ips
        ON ips_item_id = epp_entity_id
    LEFT JOIN (SELECT * FROM pagelinks WHERE pl_namespace = 0 AND pl_from_namespace = 0) AS links
        ON links.pl_title = CONCAT('Q', epp_entity_id)
    LEFT JOIN (SELECT DISTINCT ug_user FROM user_groups WHERE ug_group = 'bot') AS bots
        ON ug_user = rev_user
    LEFT JOIN (SELECT * FROM revision WHERE rev_comment LIKE '/* wbsetsitelink-remove:%') AS rev2
        ON rev2.rev_page = rev1.rev_page AND rev2.rev_id > rev1.rev_id
WHERE ips_item_id IS NULL
AND rev2.rev_timestamp IS NULL
AND ug_user IS NULL
GROUP BY epp_entity_id HAVING COUNT(links.pl_from) = 0
ORDER BY rev1.rev_id
LIMIT 2000;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...