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 35 posts 135 karma points
    Nov 03, 2015 @ 09:52
    Sam Marshallsay
    0

    [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?

    Thanks!

    UPDATE umbracoNode
    SET ParentID = 50658, umbracoNode.path = '-1,50658,' + CONVERT(nvarchar(MAX), umbracoNode.id)
    WHERE umbracoNode.ID IN (
        SELECT umbracoNode.id
        FROM umbracoNode umbracoNode
        LEFT JOIN umbracoNode parent ON parent.parentID = umbracoNode.id
        INNER JOIN cmsContent content ON content.nodeId = umbracoNode.id
        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 umbracoNode.id = published.nodeId
        LEFT JOIN (
            SELECT nodeId, versionId FROM cmsDocument WHERE newest = 1 GROUP BY nodeId, versionId
        ) as latest ON umbracoNode.id = latest.nodeId
        LEFT JOIN (
            SELECT contentNodeId, versionId, dataNvarchar FROM cmsPropertyData 
            INNER JOIN umbracoNode ON cmsPropertyData.contentNodeId = umbracoNode.id 
            INNER JOIN cmsPropertyType ON cmsPropertyType.id = 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 umbracoNode.id = property.contentNodeId
        WHERE (umbracoNode.nodeObjectType = 'b796f64c-1f99-4ffb-b886-4bf4bc011a9c')
        AND ([umbracoNode].[parentID] = -1)
        AND contenttype.alias LIKE 'Image'
        GROUP BY umbracoNode.id, 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:

Draft