Interwiki file links from wikis to Commons

Fork of Bad links from wikis to Commons: redirects by Achim55

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

SQL

#links from dewiki to redirecting pages on Commons
USE dewiki_p;
SELECT now() AS timestamp;
#interwiki links to redirecting galleries
(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 = iwl_title AND cp.page_namespace = 0 AND cp.page_is_redirect = 1 AND cp.page_title != 'HkDE-wt_Legende'
WHERE iwl_prefix ='commons'
AND iwl_title NOT LIKE '%:%'
AND iwl_title NOT LIKE '%\%%'
AND iwl_title NOT LIKE '%#%'
)
ORDER BY wpage
LIMIT 100;

SQL

Checking query status...