-- Replace all associations with ROFSTID = 4
Update Associations Set ROFSTID = 4
-- Fix roimages
Update ROImages set RODBID=2 where RODBID=1 and FileName Not In (Select FileName from roimages where RODBID=2)
-- fix figures
Update FF Set FF.ImageID = (select ImageID from ROImages RI1 where RI1.RODBID=2 and RI.FileName = RI1.FileName)   
	from Figures FF join ROIMages RI ON FF.ImageID = RI.ImageID Where RODBID = 1
Update FF Set FF.ROFSTID = 4 from figures FF where ImageID not in(select imageid from figures where ROFSTID = 4)
-- remove unused roimages
DELETE ROImages where RODBID=1
-- remove unused Figures
DELETE FROM Figures WHERE ROFSTID != 4-- Delete unused ROFSTs
-- remove unused ROFSTs
DELETE FROM ROFSTS WHERE ROFSTID != 4
-- update ROUsages to point to RODBID = 2
Update ROUSages set RODBID =2 where RODBID=1
-- update DROUsages to point to RODBID = 2
Update DROUSages set RODBID =2 where RODBID=1
-- update tblContents Text
EXEC('DISABLE TRIGGER tr_tblContents_Update ON tblContents')
Update tblContents Set Text = Replace(TEXT,' 1[END>',' 2[END>') where Text like '% 1\[END>%' escape '\'
EXEC('ENABLE TRIGGER tr_tblContents_Update ON tblContents')
-- update tblGrids.Data
EXEC('DISABLE TRIGGER tr_tblGrids_Update ON tblGrids')
update tblGrids
set Data = CAST(Replace(CAST([Data] as nvarchar(max)),' 1[END>',' 2[END>') as XML)
where Cast([Data] as nvarchar(max)) like '% 1\[END>%' escape '\'
EXEC('ENABLE TRIGGER tr_tblGrids_Update ON tblGrids')
-- Remove unused RODBs
Delete RODBs Where RODBID = 1