
-- Identify duplicates, insert into a temp table 
		-- step 1 temp table schema
		drop table if exists zDupPlaceTable;

		CREATE TEMP TABLE zDupPlaceTable (
				primaryid INTEGER,
				dupid INTEGER,
				primaryname TEXT
		);
		-- step 2 load table with duplicate records based on place name
		
		INSERT INTO zDupPlaceTable (primaryid, dupid, primaryname)
		SELECT p1.PlaceID AS primaryid,
					 p2.PlaceID AS dupid,
					 p1.Name AS primaryname
		FROM PlaceTable p1
		JOIN PlaceTable p2 ON p1.PlaceType = p2.PlaceType
											 AND UPPER(p1.Name) = UPPER(p2.Name)
											 AND p1.PlaceID < p2.PlaceID
		WHERE p1.PlaceType = 0
		ORDER BY p1.Name;
		
		--step 3 verify contents (optional)
		
		SELECT * FROM zDupPlaceTable;
		
		
-- 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


		-- step 1 temp table schema
		drop table if exists zDupPlaceDetailTable;

		CREATE TEMP TABLE zDupPlaceDetailTable (
				detail_primaryid INTEGER,
				detail_dupid INTEGER,
				MasterID INTEGER,
				primaryname TEXT
		);
		-- step 2 load table with duplicate records based on place name
		
		INSERT INTO zDupPlaceDetailTable (detail_primaryid, detail_dupid, MasterID, primaryname)
		SELECT p1.PlaceID AS detail_primaryid,
					 p2.PlaceID AS detail_dupid,
					 p1.MasterID, 
					 p1.Name AS primaryname
		FROM PlaceTable p1
		JOIN PlaceTable p2 ON p1.PlaceType = p2.PlaceType
											 AND UPPER(p1.Name) = UPPER(p2.Name)
											 AND p1.PlaceID < p2.PlaceID
		WHERE p1.PlaceType = 0
		ORDER BY p1.Name;
		
		--step 3 verify contents (optional)
		
		SELECT * FROM zDupPlaceDetailTable;

-- 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);
				
		DROP TABLE zDupPlaceDetailTable;
		DROP TABLE zDupPlaceTable;
