This query is marked as a draft This query has been published by Achim55.

SQL

AخA
 
#USE stop;
USE enwiki_p;
SELECT now() AS timestamp;
#interwikilinks pointing to redirects
(SELECT CONCAT ('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,
        CONCAT ('→ [[:c:', REPLACE (iwl_title, '_', ' '), ']]') AS commonscat
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace = 0
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(iwl_title, 10)
AND cp.page_namespace = 14
INNER JOIN commonswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
WHERE iwl_prefix ='commons'
AND iwl_title LIKE 'Category:%'
AND (cp.page_is_redirect = 1 OR ccl.cl_to = 'Category_redirects')
)
UNION
#external links pointing to redirects
(SELECT CONCAT('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,
        CONCAT('→ [[:c:Category:', REPLACE (cp.page_title, '_', ' '), ']]') AS commonscat
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace = 0
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(el_to, 45)
AND cp.page_namespace = 14
INNER JOIN commonswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
WHERE el_to LIKE 'https://commons.wikimedia.org/wiki/Category:%'
AND (cp.page_is_redirect = 1 OR ccl.cl_to = 'Category_redirects')
)
ORDER BY wpage
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.

Query status: superseded