Skip to content

Instantly share code, notes, and snippets.

@alantsai

alantsai/sql-delete-data-history.sql

Created November 29, 2015 17:42
Show Gist options
  • Save alantsai/bf0291a45bb199e3b577 to your computer and use it in GitHub Desktop.
Save alantsai/bf0291a45bb199e3b577 to your computer and use it in GitHub Desktop.
Download ZIP
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
Raw
sql-delete-data-history.sql
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment