Operations

Sanity checks

1. Multiple IsPreferred instances

SELECT Fk_Form, Fk_Context, count(*) as [count] FROM Instance WHERE IsPreferred = 1
GROUP BY Fk_Form, Fk_Context HAVING count(*) > 1

If you get any result from the query, resolve the issue by following the 1. Resolve multiple IsPreferred instances guide below.

Resolving issues

1. Resolve multiple IsPreferred instances

BEGIN TRANSACTION

-- Select the multiple IsPreferred instances into temp table
PRINT('Create #temp1')
SELECT Fk_Form, Fk_Context, count(*) as [count] INTO #temp1 FROM Instance WHERE IsPreferred = 1 GROUP BY Fk_Form, Fk_Context HAVING count(*) > 1

-- Fix problem with multiple IsPreferred instances
PRINT('Create #temp2 for instances where we prefer newest')
SELECT t.* INTO #temp2 FROM #temp1 t JOIN Context c ON (c.Id = t.Fk_Context) WHERE IsNewestPreferred = 1
PRINT('Create #temp3 for instances where we prefer oldest')
SELECT t.* INTO #temp3 FROM #temp1 t JOIN Context c ON (c.Id = t.Fk_Context) WHERE IsNewestPreferred = 0

PRINT('Find instances where we should prefer newest version')
SELECT i.* FROM #temp2 t JOIN Instance i ON (i.Fk_Context = t.Fk_Context AND i.Fk_Form = t.Fk_Form) ORDER BY i.Fk_Form, i.Fk_Context, i.Created_Utc DESC
PRINT('Find instances where we should prefer oldest version')
SELECT i.* FROM #temp3 t JOIN Instance i ON (i.Fk_Context = t.Fk_Context AND i.Fk_Form = t.Fk_Form) ORDER BY i.Fk_Form, i.Fk_Context, i.Created_Utc ASC

PRINT('Update instances where we prefer newest version')
UPDATE old SET old.IsPreferred = 0 
  FROM #temp2 t
  JOIN Instance new ON (new.Fk_Form = t.Fk_Form AND new.Fk_Context = t.Fk_Context AND new.IsPreferred = 1)
  JOIN Instance old ON (old.Fk_Form = t.Fk_Form AND old.Fk_Context = t.Fk_Context AND old.IsPreferred = 1 AND old.Created_Utc < new.Created_Utc)

PRINT('Update instances where we prefer oldest version')
UPDATE new SET new.IsPreferred = 0 
  FROM #temp2 t
  JOIN Instance old ON (old.Fk_Form = t.Fk_Form AND old.Fk_Context = t.Fk_Context AND old.IsPreferred = 1)
  JOIN Instance new ON (new.Fk_Form = t.Fk_Form AND new.Fk_Context = t.Fk_Context AND new.IsPreferred = 1 AND new.Created_Utc > old.Created_Utc)

PRINT('Resulted instances where we prefer newest version')
SELECT i.* FROM #temp2 t JOIN Instance i ON (i.Fk_Context = t.Fk_Context AND i.Fk_Form = t.Fk_Form) ORDER BY i.Fk_Form, i.Fk_Context, i.Created_Utc DESC
PRINT('Resulted instances where we prefer oldest version')
SELECT i.* FROM #temp3 t JOIN Instance i ON (i.Fk_Context = t.Fk_Context AND i.Fk_Form = t.Fk_Form) ORDER BY i.Fk_Form, i.Fk_Context, i.Created_Utc ASC

-- COMMIT TRANSACTION
-- ROLLBACK TRANSACTION