/*
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 1123 and 1210. These will need to be replaced
in all locations in the script with the actual FactTypeID's of interest.

*/



UPDATE EventTable
SET PlaceID = 

(
   SELECT C.PlaceID1
   FROM
   (
    SELECT A.OwnerID, E1.EventID AS EventID1, FT1.Name AS FT_Name1, E1.PlaceID AS PlaceID1, P1.Name AS P_Name1,
                      E2.EventID AS EventID2, FT2.Name AS FT_Name2, E2.PlaceID AS PlaceID2, P2.Name AS P_Name2
    FROM
    (          
       SELECT COUNT(*) AS f_count, E.EventID, E.EventType, E.OwnerID,E.PlaceID
       FROM EventTable AS E
       WHERE E.EventType IN (1123,1210)
       GROUP BY E.OwnerID
       HAVING F_count = 2
    ) AS A
    JOIN EventTable AS E1 ON E1.OwnerID = A.OwnerID AND E1.EventType = 1123
    JOIN FactTypeTable AS FT1 ON FT1.FactTypeID = E1.EventType
    JOIN PlaceTable AS P1 ON P1.PlaceID = E1.PlaceID
    JOIN EventTable AS E2 ON E2.OwnerID = A.OwnerID AND E2.EventType = 1210
    JOIN FactTypeTable AS FT2 ON FT2.FactTypeID = E2.EventType
    JOIN PlaceTable AS P2 ON P2.PlaceID = E2.PlaceID
    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, FT1.Name AS FT_Name1, E1.PlaceID AS PlaceID1, P1.Name AS P_Name1,
                      E2.EventID AS EventID2, FT2.Name AS FT_Name2, E2.PlaceID AS PlaceID2, P2.Name AS P_Name2
    FROM
    (          
       SELECT COUNT(*) AS f_count, E.EventID, E.EventType, E.OwnerID,E.PlaceID
       FROM EventTable AS E
       WHERE E.EventType IN (1123,1210)
       GROUP BY E.OwnerID
       HAVING F_count = 2
    ) AS A
    JOIN EventTable AS E1 ON E1.OwnerID = A.OwnerID AND E1.EventType = 1123
    JOIN FactTypeTable AS FT1 ON FT1.FactTypeID = E1.EventType
    JOIN PlaceTable AS P1 ON P1.PlaceID = E1.PlaceID
    JOIN EventTable AS E2 ON E2.OwnerID = A.OwnerID AND E2.EventType = 1210
    JOIN FactTypeTable AS FT2 ON FT2.FactTypeID = E2.EventType
    JOIN PlaceTable AS P2 ON P2.PlaceID = E2.PlaceID
    WHERE PlaceID1 != PlaceID2
    ORDER BY A.Ownerid
    ) AS C WHERE C.EventID2 = EventTable.EventID
) -- end of WHERE EventTable.EventID IN