/* update_placeid-THmods2.sql
Stripped-down version of 
update_placeid.sql
1/29/2024   Jerry Bryan

For two given fact types and for persons who have events of both fact type,
updates events of the second fact type to have the same PlaceID as events
of the first fact type.

This script uses subquery to find the relevent events which need to be updated.
The subquery fetches more information than is really needed to do the update. But
the extra information does no harm and I have not removed the extra code from
the script.

This script is hardwired with FactTypeID's 1120 and 1121. These will need to be replaced
in all locations in the script with the actual FactTypeID's of interest.

*/

/* Reset target event to have no Place
UPDATE EventTable
SET PlaceID=0, SiteID=0
WHERE EventType=1121
;
*/


UPDATE EventTable
SET PlaceID = 

(
   SELECT C.PlaceID1
   FROM
   (
    SELECT
     A.OwnerID
     , E1.EventID AS EventID1
     , E1.PlaceID AS PlaceID1
     , E2.EventID AS EventID2
     , E2.PlaceID AS PlaceID2
    FROM
    (          
       SELECT COUNT(*) AS f_count, E.EventID, E.EventType, E.OwnerID,E.PlaceID
       FROM EventTable AS E
       WHERE E.EventType IN (1120,1121)
       GROUP BY E.OwnerID
       HAVING F_count = 2
    ) AS A
    JOIN EventTable AS E1 ON E1.OwnerID = A.OwnerID AND E1.EventType = 1120
    JOIN EventTable AS E2 ON E2.OwnerID = A.OwnerID AND E2.EventType = 1121
    WHERE PlaceID1 != PlaceID2
    ORDER BY A.Ownerid
    ) AS C WHERE C.EventID2 = EventTable.EventID
) -- end of SET PlaceID =

WHERE EventTable.EventID IN
(
   SELECT C.EventID2
   FROM
   (
    SELECT
     A.OwnerID
     , E1.EventID AS EventID1
     , E1.PlaceID AS PlaceID1
     , E2.EventID AS EventID2
     , E2.PlaceID AS PlaceID2
      FROM
    (          
       SELECT COUNT(*) AS f_count, E.EventID, E.EventType, E.OwnerID,E.PlaceID
       FROM EventTable AS E
       WHERE E.EventType IN (1120,1121)
       GROUP BY E.OwnerID
       HAVING F_count = 2
    ) AS A
    JOIN EventTable AS E1 ON E1.OwnerID = A.OwnerID AND E1.EventType = 1120
    JOIN EventTable AS E2 ON E2.OwnerID = A.OwnerID AND E2.EventType = 1121
    WHERE PlaceID1 != PlaceID2
    ORDER BY A.Ownerid
    ) AS C WHERE C.EventID2 = EventTable.EventID
) -- end of WHERE EventTable.EventID IN