-- Facts-ChangeType.sql
/*
2014-10-21 Tom Holden ve3meo
2014-10-22 rev: Steps; added constraint on UPDATE; formatted.

Requires SQLite Expert Personal with fake RMNOCASE extension loaded or an equivalent 
SQLite manager that also supports the entry of runtime variables.

Highlight and execute Step 1 which requires that you enter accurately the
Abbrev values for the Fact Types you are changing from and to, from the Fact Type
List. Check the temp tables xEventOrigTable and xEventTargTable that they have 
but one record each and with a non-zero FactTypeID. If not, re-execute and 
correct your entries, else...

When satisfied, then highlight and execute Step 2. 

*/
---- STEP 1 ----
DROP TABLE

IF EXISTS xEventOrigTable;
	CREATE TEMP TABLE xEventOrigTable AS

SELECT $EVENTTYPETOCHANGEFROM
	,FactTypeID
	,OwnerType
FROM FactTypeTable
WHERE Abbrev LIKE $EVENTTYPETOCHANGEFROM;

DROP TABLE

IF EXISTS xEventTargTable;
	CREATE TEMP TABLE xEventTargTable AS

SELECT $EVENTTYPETOCHANGETO
	,FactTypeID
	,OwnerType
FROM FactTypeTable
WHERE Abbrev LIKE $EVENTTYPETOCHANGETO
	AND OwnerType = (
		SELECT OwnerType
		FROM xEventOrigTable
		) -- restrict to same fact level, i.e., Indiv or Fam
	;

---- STEP 2 ----
UPDATE EventTable
SET EventType = (
		SELECT FactTypeID
		FROM xEventTargTable
		)
WHERE EventType = (
		SELECT FactTypeID
		FROM xEventOrigTable
		) -- only update those events whose EventTypeID matches the FactTypeID of $EventTypeToChangeFrom
	AND (
		SELECT FactTypeID
		FROM xEventTargTable
		) -- and only if there is a valid FactTypeID for $EventTypeToChangeTo
	;
	---- End of Script ----