AlanTsai
11/29/2015 - 5:42 PM

umbraco delete data version last then certain date. 刪除某個日期之前的歷史資料. Source: https://our.umbraco.org/projects/backoffice-extensions/falm-hou

umbraco delete data version last then certain date. 刪除某個日期之前的歷史資料. Source: https://our.umbraco.org/projects/backoffice-extensions/falm-housekeeping/bugs-reports/42409-The-wait-operation-timed-out #umbraco

sql-delete-data-history.sql
content_copyfile_download
DECLARE @versionDate date;

SET @versionDate = CONVERT(DATETIME, 11/26/2015, 102); -- delete data older than provide date

SELECT nodeId, published, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId, newest into #tmp 

FROM cmsDocument WHERE versionID NOT IN 

(SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN 

(SELECT versionId FROM 

(SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum FROM cmsContentVersion CV 

JOIN cmsDocument DD ON CV.versionId = DD.versionId WHERE DD.nodeId = D.nodeId 

AND CV.versionDate < @versionDate) AS tmp
WHERE tmp.published = 1 OR tmp.newest = 1))

DELETE FROM cmsPreviewXml WHERE VersionId IN (SELECT #tmp.VersionId FROM #tmp WHERE #tmp.published = 0 AND #tmp.newest = 0) 

DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT #tmp.VersionId FROM #tmp WHERE #tmp.published = 0 AND #tmp.newest = 0)

DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT #tmp.VersionId FROM #tmp WHERE #tmp.published = 0 AND #tmp.newest = 0)

DELETE FROM cmsDocument WHERE VersionId IN (SELECT #tmp.VersionId FROM #tmp WHERE #tmp.published = 0 AND #tmp.newest = 0)

DROP TABLE #tmp