-- EventNoteToDescription-Move.sql
/*
2014-08-17 Tom Holden ve3meo

Moves content from Event Note to empty but enabled Event Description 
if note is 100 chars or less for Fact Type whose abbreviation (the label as 
seen in the Edit Person screen) matches the value entered when run. 
*/
BEGIN TRANSACTION;

UPDATE
	OR

ROLLBACK EventTable

SET Details = Note -- Description filled with Note
	,Note = CAST('' AS TEXT) -- Note emptied
WHERE EventID IN (
		-- List of EventIDs for particular fact type having an empty but enabled 
		-- description and a non-empty note length no longer than 100 chars.
		SELECT EventID --, Description, Note -- uncomment to view
		FROM (
			-- list of event ID, Description, Note for particular fact type
			SELECT EventID
				,CAST(Details AS TEXT) AS Description
				,CAST(Note AS TEXT) AS Note
			FROM EventTable E
			INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
			WHERE F.UseValue -- Description field is enabled
				AND E.EventType IN (
					-- FactTypeID of Fact Type whose abbreviation matches the runtime value entered
					SELECT FactTypeID
					FROM FactTypeTable F
					WHERE Abbrev LIKE $ABBREV -- enter the label from the Edit Person screen at runtime
					)
			)
		WHERE LENGTH(Description) = 0 -- empty Description
			AND LENGTH(NOTE) > 0 -- non-empty Note
			AND LENGTH(NOTE) < 101 -- Note no longer than 100 chars
		);

COMMIT TRANSACTION;
	-- END