Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

  • Sam Marshallsay 32 posts 122 karma points
    Nov 03, 2015 @ 09:52
    Sam Marshallsay

    [DB SQL] Moving large numbers of media elements

    Hi guys,

    I have a site that has a large (2-3k) number of media items that have been uploaded to the root of the media folder.

    This is what I'm planning to use for this (ignore the excessive number of joins, I cannibalised it from another piece of SQL).

    Are there any other columns I should be updating in addition to ParentId and Path? I assume I should update Level as well? Should this be updated to 2 if I am moving all the root media to a subfolder located off the root?


    UPDATE umbracoNode
    SET ParentID = 50658, umbracoNode.path = '-1,50658,' + CONVERT(nvarchar(MAX),
    WHERE umbracoNode.ID IN (
        FROM umbracoNode umbracoNode
        LEFT JOIN umbracoNode parent ON parent.parentID =
        INNER JOIN cmsContent content ON content.nodeId =
        LEFT JOIN cmsContentType contenttype ON contenttype.nodeId = content.contentType
        LEFT JOIN (
            SELECT nodeId, versionId FROM cmsDocument WHERE published = 1 GROUP BY nodeId, versionId
        ) as published ON = published.nodeId
        LEFT JOIN (
            SELECT nodeId, versionId FROM cmsDocument WHERE newest = 1 GROUP BY nodeId, versionId
        ) as latest ON = latest.nodeId
        LEFT JOIN (
            SELECT contentNodeId, versionId, dataNvarchar FROM cmsPropertyData 
            INNER JOIN umbracoNode ON cmsPropertyData.contentNodeId = 
            INNER JOIN cmsPropertyType ON = cmsPropertyData.propertytypeid 
            INNER JOIN cmsDataType ON cmsPropertyType.dataTypeId = cmsDataType.nodeId
            WHERE  umbracoNode.nodeObjectType = 'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'
            AND [umbracoNode].[parentID] = -1
        ) as property
        ON = property.contentNodeId
        WHERE (umbracoNode.nodeObjectType = 'b796f64c-1f99-4ffb-b886-4bf4bc011a9c')
        AND ([umbracoNode].[parentID] = -1)
        AND contenttype.alias LIKE 'Image'
        GROUP BY, umbracoNode.trashed, umbracoNode.parentID, umbracoNode.nodeUser, umbracoNode.level, umbracoNode.path, umbracoNode.sortOrder, umbracoNode.uniqueID, umbracoNode.text, 
        umbracoNode.nodeObjectType, umbracoNode.createDate, published.versionId, latest.versionId, contenttype.alias, contenttype.icon, contenttype.thumbnail, property.dataNvarchar
Please Sign in or register to post replies

Write your reply to: