Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT set_config('rgkh_journal.current_user', '1', false);
- WITH cte_inf_objects AS (
- SELECT
- o.id AS inf_object_id,
- o.mo_affiliation_id AS geo_tag_id,
- cwss.name AS cwss_name,
- o.name AS object_name,
- o.type_id AS object_type_id,
- o.object_id AS object_id
- FROM pure_water.inf_object o
- JOIN pure_water.cwss cwss ON cwss.id = o.cwss_affiliation_id
- WHERE
- o.deleted_at IS NULL
- AND cwss.deleted_at IS NULL
- ), cte_match AS (
- SELECT
- cio.inf_object_id AS inf_object_id,
- pwpdo.id AS documents_object_id
- FROM pure_water.pure_water_program_documents_object pwpdo
- LEFT JOIN cte_inf_objects cio ON
- cio.geo_tag_id = pwpdo.geo_tag_id
- AND cio.cwss_name = pwpdo.cwss_name
- AND cio.object_name = pwpdo.object_name
- AND cio.object_type_id = pwpdo.object_type_id
- AND cio.object_id = pwpdo.object_id
- WHERE pwpdo.deleted_at IS NULL
- ), cte_not_unique AS (
- SELECT
- documents_object_id
- FROM cte_match
- GROUP BY documents_object_id
- HAVING count(documents_object_id) >1
- )
- DELETE FROM pure_water.pure_water_program_documents_object WHERE id IN (
- SELECT documents_object_id FROM cte_not_unique
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement