Re: [Gnumed-devel] GNUmed upgrade preparation alert

[Jim Busser]

Here is the complete set of alternate queries.

Note that what is being returned is the primary key (id) of the row being 
returned, which facilitates to reference them as the

        desired-to-be-kept
        desired-to-be-later-deleted

values to use when updating any existing records which will first need to be 
re-pointed.

The queries below are followed by update statements which can be used and 
potentially-suitable deletion queries for the duplicates.

*** It goes without saying that you should first try this on a test system
*** and not to run it on production except after a backup.


-- ============================================================
-- queries to identify and visually review the duplicated streets, urbs and 
regions
-- ============================================================

-- for street
select id, name, postcode from dem.street
where id_urb || lower(name) || lower(postcode) in (
select id_urb || lower(name) || lower(postcode) from dem.street
group by id_urb || lower(name) || lower(postcode)
having count(*) > 1
)
order by id_urb, lower(name), lower (postcode)
;

-- for urb
select id, name, postcode from dem.urb
where id_state || lower(name) || lower(postcode) in (
select id_state || lower(name) || lower(postcode) from dem.urb
group by id_state || lower(name) || lower(postcode)
having count(*) > 1
)
order by id_state, lower(name), lower(postcode)
;


-- for regions (states, provinces)
select id, code, name, country from dem.state
where code || lower(name) || lower(country) in (
select code || lower(name) || lower(country) from dem.state
group by code || lower(name) || lower(country)
having count(*) > 1
)
order by code, lower(name), lower(country)
;

-- =========================================================================
-- queries for updating the keys that will need repointing
-- CAUTION
-- be extremely careful when performing these
-- you might rather pre-assemble them, and re-check all visually, before 
running in a transaction
-- =========================================================================

-- ---------------------------------------------------------------------------
-- before resolving duplications in street, take care of:
-- dem.address(id_street) REFERENCES dem.street(id)
-- ---------------------------------------------------------------------------

UPDATE dem.address SET id_street = newStreetId WHERE id_street = oldStreetId ;

-- multi line format:
UPDATE dem.address
SET id_street = newStreetId
WHERE id_street = oldStreetId
;


-- ---------------------------------------------------------------------------
-- before resolving duplications in urb, take care of:
-- dem.street(id_urb) REFERENCES dem.urb(id)
-- ---------------------------------------------------------------------------

UPDATE dem.street SET id_urb = newUrbId WHERE id_urb = oldUrbId ;

-- multi line format:
UPDATE dem.street
SET id_urb = newUrbId
WHERE id_urb = oldUrbId
;


-- ---------------------------------------------------------------------------
-- before resolving duplications in regions, take care of:
-- dem.urb(id_state) REFERENCES dem.state(id)
-- ---------------------------------------------------------------------------

UPDATE dem.urb SET id_state = newStateId WHERE id_state = oldStateId ;

-- multi line format:
UPDATE dem.urb
SET id_state = newStateId
WHERE id_state = oldStateId
;


-- =========================================================================
-- deleting the duplicates
-- =========================================================================

Once any addresses (streets, cities) have been repointed as necessary, 
duplicates which are no longer keyed can be deleted:

        delete from dem.street where id not in (select id_street from 
dem.address)
        and id in (select id from dem.street
                where id_urb || lower(name) || lower(postcode) in (
                select id_urb || lower(name) || lower(postcode) from dem.street
                group by id_urb || lower(name) || lower(postcode)
                having count(*) > 1
                )
        );

        delete from dem.urb where id not in (select id_urb from dem.street)
        and id in (select id from dem.urb
                where id_state || lower(name) || lower(postcode) in (
                select id_state || lower(name) || lower(postcode) from dem.urb
                group by id_state || lower(name) || lower(postcode)
                having count(*) > 1
                )       
        );

        delete from dem.state where id not in (select id_state from dem.urb)
        and id in (select id from dem.state
                where code || lower(name) || lower(country) in (
                select code || lower(name) || lower(country) from dem.state
                group by code || lower(name) || lower(country)
                having count(*) > 1
                )
        );
-- Jim