--FTMimport-MoveEventPlaceDetail2Description.sql
/*
2016-03-20 Tom Holden ve3meo

Moves PlaceDetail.Name to Event Description and deletes PlaceDetail.
Makes a temp table with the new description from the Place Detail.Name.
Cleans up EventTable and PlaceTable after. 
Temp table is lost when SQLite manager closes session.

*/
-- make the temp table 
DROP TABLE

IF EXISTS xNewDesc;
	CREATE TEMP TABLE xNewDesc AS

SELECT P.PlaceID AS SiteID
	,P.NAME AS NewDescription
FROM EventTable E
INNER JOIN PlaceTable P ON E.SiteID = P.PlaceID -- only those events that have a corresponding place detail record in PlaceTable
	AND E.SiteID > 0 -- event must have a place detail
	AND P.PlaceType = 2 -- PlaceTable record must be for a place detail
	;

-- revise the Event Description to the PlaceDetail.Name from the temp table
UPDATE EventTable
SET Details = (
		SELECT NewDescription
		FROM xNewDesc X
		WHERE EventTable.SiteID = X.SiteID
		)
WHERE SiteID IN (
		SELECT DISTINCT SiteID
		FROM xNewDesc
		);

-- unlink the PlaceDetails in the EventTable
UPDATE EventTable
SET SiteID = 0
WHERE SiteID IN (
		SELECT DISTINCT SiteID
		FROM xNewDesc
		);

-- delete the PlaceDetails from PlaceTable
DELETE
FROM PlaceTable
WHERE PlaceID IN (
		SELECT DISTINCT SiteID
		FROM xNewDesc
		);