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')
)

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);

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 dp.page_title, dr.rdf_title, dr.rdt_title from disamb_page dp, disamb_redirect dr
where dp.page_title like '%\_(disambiguation)' and dr.rdt_title != dp.page_title
and dr.rdf_title = substring(dp.page_title, 1, INSTR(dp.page_title, '_(disambiguation)')-1);

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 dr.rdf_title, dr.rdt_title, dp.page_title from disamb_page dp, disamb_redirect dr
where dr.rdf_title like '%\_(disambiguation)' and dr.rdt_title != dp.page_title
and dp.page_title = substring(dr.rdf_title, 1, INSTR(dr.rdf_title, '_(disambiguation)')-1);

with 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 dr1.rdf_title, dr1.rdt_title, dr2.rdf_title, dr2.rdt_title from disamb_redirect dr1, disamb_redirect dr2
where dr1.rdf_title like '%\_(disambiguation)' and dr1.rdt_title != dr2.rdt_title
and dr2.rdf_title = substring(dr1.rdf_title, 1, INSTR(dr1.rdf_title, '_(disambiguation)')-1);

/*
(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...
