-- Must be run after  review column is added to the review table 
-- Last statement is different
-- Run Stored Procedures after this

ALTER TABLE dm.ReviewAggregate
  ADD reviewid int;
  
UPDATE
    ra
SET
    ra.reviewId = rev.id    
FROM
    hib.review AS rev    
    INNER JOIN [dm].[ReviewAggregate] as ra
        ON ra.requestID = rev.requestId
WHERE
    ra.reviewId is null and ra.reviewType = 'Homeless Registry' and rev.groupId = 16; 

-------------------------------------------------------------------------------------
UPDATE
    ra
SET
    ra.reviewId = rev.id
FROM
    hib.review AS rev    
    INNER JOIN [dm].[ReviewAggregate] as ra
        ON ra.requestID = rev.requestId
WHERE
    ra.reviewId is null and ra.reviewType = 'Capri' and rev.groupId = 13;
    
-------------------------------------------------------------------------------------   

UPDATE
    ra
SET
    ra.reviewId = rev.id
FROM
    hib.review AS rev    
    INNER JOIN [dm].[ReviewAggregate] as ra
        ON ra.requestID = rev.requestId
WHERE
    ra.reviewId is null and ra.reviewType = 'ORD' and rev.groupId = 7;
    
-------------------------------------------------------------------------------------

UPDATE
    ra
SET
    ra.reviewId = rev.id
FROM
    hib.review AS rev    
    INNER JOIN [dm].[ReviewAggregate] as ra
        ON ra.requestID = rev.requestId
WHERE
    ra.reviewId is null and ra.reviewType = 'Security' and rev.groupId = 5;
    
-------------------------------------------------------------------------------------
    
UPDATE
    ra
SET
    ra.reviewId = rev.id
FROM
    hib.review AS rev    
    INNER JOIN [dm].[ReviewAggregate] as ra
        ON ra.requestID = rev.requestId
WHERE
    ra.reviewId is null and ra.reviewType = 'Privacy' and rev.groupId = 3;
    
-------------------------------------------------------------------------------------

UPDATE
    ra
SET
    ra.reviewId = rev.id
FROM
    hib.review AS rev    
    INNER JOIN [dm].[ReviewAggregate] as ra
        ON ra.requestID = rev.requestId
WHERE
    ra.reviewId is null and ra.reviewType = 'SQDUG' and rev.groupId = 11;
    
-------------------------------------------------------------------------------------

UPDATE
    ra
SET
    ra.reviewId = rev.id
FROM
    hib.review AS rev    
    INNER JOIN [dm].[ReviewAggregate] as ra
        ON ra.requestID = rev.requestId
WHERE
    ra.reviewId is null and ra.reviewType = 'OEF' and rev.groupId = 9;
    
--------------------------------------------------------------------------------------

UPDATE
    ev
SET
    ev.reviewId = rev.id
FROM
    hib.review AS rev    
    INNER JOIN hib.event as ev
        ON ev.requestid = rev.requestId
    inner join hib.eventgroup eg on ev.id = eg.eventid
WHERE
    ev.reviewId is null and eg.groupid = rev.groupId