-- generate list of duplicate places

DROP TABLE IF EXISTS zDupPlaceTable;
CREATE /* TEMP */ TABLE zDupPlaceTable
AS
    select min(p1.placeid) primaryid, p2.PlaceID dupid, p1.name primaryname
    from PlaceTable p1,
         PlaceTable p2
    where p1.PlaceType = p1.PlaceType
      and p1.Name = p2.Name
      and P1.Abbrev = p2.Abbrev
      and p1.Normalized = p2.Normalized
      and p1.Latitude = p2.Latitude
      and p1.Longitude = p2.Longitude
      and p1.LatLongExact = p2.LatLongExact
      and p1.MasterID = p2.MasterID
      and p1.Note = p2.Note
      and p1.Reverse = p2.Reverse
      and p1.fsID = p2.fsID
      and p1.anID = p2.anID
      and p1.PlaceID < p2.PlaceID
      and p1.PlaceType = 0
    group by p2.placeid
    order by primaryname;

-- update place detail back references to place record

UPDATE PlaceTable
SET MasterID = (select zDupPlaceTable.primaryid from zDupPlaceTable where PlaceTable.MasterID = zDupPlaceTable.dupid)
WHERE MasterID in (select dupid from zDupPlaceTable);

-- generate duplicate place detail entries

DROP TABLE IF EXISTS zDupPlaceDetailTable;
CREATE /* TEMP */ TABLE zDupPlaceDetailTable
AS
    select min(p1.placeid) detail_primaryid, p2.PlaceID detail_dupid, p1.MasterID, p1.name primaryname
    from PlaceTable p1,
         PlaceTable p2
    where p1.PlaceType = p1.PlaceType
      and p1.Name = p2.Name
      and P1.Abbrev = p2.Abbrev
      and p1.Normalized = p2.Normalized
      and p1.Latitude = p2.Latitude
      and p1.Longitude = p2.Longitude
      and p1.LatLongExact = p2.LatLongExact
      and p1.MasterID = p2.MasterID
      and p1.Note = p2.Note
      and p1.Reverse = p2.Reverse
      and p1.fsID = p2.fsID
      and p1.anID = p2.anID
      and p1.PlaceID < p2.PlaceID
      and p1.PlaceType = 2
    group by p2.placeid
    order by primaryname;

-- repoint place and details duplicate references

UPDATE EventTable
SET PlaceID = (select zDupPlaceTable.primaryid from zDupPlaceTable where EventTable.PlaceID = zDupPlaceTable.dupid)
WHERE PlaceID in (select dupid from zDupPlaceTable);

UPDATE EventTable
SET SiteID = (select zDupPlaceDetailTable.detail_primaryid
              from zDupPlaceDetailTable
              where EventTable.SiteID = zDupPlaceDetailTable.detail_dupid)
WHERE PlaceID in (select dupid from zDupPlaceTable);

UPDATE CitationLinkTable
SET OwnerID = (select zDupPlaceTable.primaryid
               from zDupPlaceTable
               where CitationLinkTable.OwnerID = zDupPlaceTable.dupid
                 and CitationLinkTable.OwnerType = 5)
WHERE OwnerTYpe = 5
  and OwnerID in (select dupid from zDupPlaceTable);

UPDATE MediaLinkTable
SET OwnerID = (select zDupPlaceTable.primaryid
               from zDupPlaceTable
               where MediaLinkTable.OwnerID = zDupPlaceTable.dupid
                 and MediaLinkTable.OwnerType = 5)
WHERE OwnerTYpe = 5
  and OwnerID in (select dupid from zDupPlaceTable);

UPDATE URLTable
SET OwnerID = (select zDupPlaceTable.primaryid
               from zDupPlaceTable
               where URLTable.OwnerID = zDupPlaceTable.dupid
                 and URLTable.OwnerType = 5)
WHERE OwnerTYpe = 5
  and OwnerID in (select dupid from zDupPlaceTable);

-- remove duplicated (and no longer referenced) place and detail entries

DELETE
FROM PlaceTable
WHERE PlaceID in (select detail_dupid from zDupPlaceDetailTable);

DELETE
FROM PlaceTable
WHERE PlaceID in (select dupid from zDupPlaceTable);