Disambiguations to be merged

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

SQL

with disamb_page as (
  select page_title from page
  where page_is_redirect = 0 and page_namespace = 0
  and page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')
),
disamb_redirect as (
  select p1.page_title as rdt_title, p2.page_title as rdf_title from page p1
  join redirect on rd_title = p1.page_title and rd_namespace = 0
  join page p2 on p2.page_id = rd_from and p2.page_namespace = 0 and p2.page_is_redirect = 1
  where p1.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')
  and p1.page_is_redirect = 0 and p1.page_namespace = 0
)

/*
select dp1.page_title, dp2.page_title from disamb_page dp1, disamb_page dp2
where dp1.page_title like '%\_(disambiguation)'
and dp2.page_title = substring(dp1.page_title, 1, INSTR(dp1.page_title, '_(disambiguation)')-1)
*/

select dp.page_title from disamb_page dp, disamb_redirect dr
where dp.page_title like '%\_(disambiguation)'
and dr.rdf_title = substring(dp.page_title, 1, INSTR(dp.page_title, '_(disambiguation)')-1)

/*
select p1.page_title, p2.page_title from page p1
join page p2 on p2.page_title = substring(p1.page_title, 1, INSTR(p1.page_title, '_(disambiguation)')-1)
and p2.page_namespace = 0 and p2.page_is_redirect = 0
join redirect on rd_from = p1.page_id and rd_namespace = 0 and rd_title != p2.page_title
join page p3 on p3.page_title = rd_title and p3.page_namespace = 0
where p1.page_title like '%\_(disambiguation)' and p1.page_namespace = 0 and p1.page_is_redirect = 1
-- and p1.page_id not in (select rd_from from redirect where rd_title = p2.page_title and rd_namespace = 0)
and p2.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')
and p3.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')
*/

/*
(select p1.page_title as dup_disamb, p2.page_title as base_disamb from page p1
 join page p2 on p2.page_title = concat(substring(p1.page_title, 1, INSTR(p1.page_title, '_(')-1), '_(disambiguation)')
 and p2.page_namespace = 0 and p2.page_is_redirect = 0
 where p1.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')
 and p1.page_namespace = 0 and p1.page_is_redirect = 0
 and p1.page_title like '%\_(%)' and p1.page_title not like '%\_(disambiguation)')
union
(select p1.page_title as dup_disamb, rd_title as base_disamb from page p1
 join page p2 on p2.page_title = concat(substring(p1.page_title, 1, INSTR(p1.page_title, '_(')-1), '_(disambiguation)')
 and p2.page_namespace = 0 and p2.page_is_redirect = 1
 join redirect on rd_from = p2.page_id and rd_namespace = 0
 where p1.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')
 and p1.page_namespace = 0 and p1.page_is_redirect = 0
 and p1.page_title like '%\_(%)' and p1.page_title not like '%\_(disambiguation)')
 */

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...