Copied to clipboard

Flag this post as spam?

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


  • Tom 711 posts 948 karma points
    Feb 18, 2011 @ 07:20
    Tom
    0

    Update Property Values On Bulk Nodes

    Hi Guys,

    I have a pile and by pile i mean over 1000 product nodes in different folders in my content tree.. by accident i didn't assign a value to a "productaddeddate" property for the bulk of them and so the date has defaulted to '1900-01-01'

     

    I was wondering how I could loop through all of them and update them or is there some sql script i could use to update the xml for all and set the value to something else for all of them where their current property value is '1900-01-01'?

     

    Cheers,

    Tom

  • Dirk De Grave 4532 posts 5977 karma points MVP 3x admin c-trib
    Feb 18, 2011 @ 09:15
    Dirk De Grave
    0

    You could do with a sql statement, need the cmsPropertyData table and find what property needs changing. But be aware there's different versions of each document in that table, so either update all of them or just the latest. By far the fastest solution (Oh, don't forget to backup your db, in case something goes ooops...)

    2nd solution is to go with the document api, which will require lots of db access and especially, if you're dealing with publishing as well, it may take a very long time to complete.

    Hope this helps.

    Regards,

    /Diri

  • GShenanigan 27 posts 119 karma points
    Feb 03, 2016 @ 14:24
    GShenanigan
    0

    Very sorry to "zombie" an old thread, but Dirk's post is something I've been doing myself. I've been updating the latest version of each property in the DB directly via SQL. It works very well for allowing my clients to feed automatic price/stock updates from their back-office system.

    However, I have an issue with this approach that I've not figured out. When I run my DB update, I'm having trouble getting Umbraco to pick up the new value and display this on the site.

    My umbraco.config file displays the new value, as does the Umbraco CMS area, however, my content on the website still shows the old value.

    It seems to be some sort of caching "somewhere", but I've not found a reliable way to get it to publish, other than manually re-publishing every node on the site.

    I've tried:

    • Deleting umbraco.config
    • Restarting the App Pool
    • Restarting the website
    • Restarting IIS
    • Calling my.domain/umbraco/dialogs/republish.aspx?xml=true

    None of these steps seems to work consistently to get the content to the live site.

    Anyone got any ideas for how to resolve this?

  • Tony Cheetham 24 posts 134 karma points
    May 03, 2016 @ 11:12
    Tony Cheetham
    0

    Zombieing too.. I've also had this issue of caching. Nothing I do will make the site load the data from the database. I have deleted every folder in the app_data folder that you can, including umbraco.config. Shut down the website, killed all IIS processes. Re-updated my database and restarted the site....Still the old data :| It's quite a silly problem. Does Umbraco even use the database anymore? :P

  • GShenanigan 27 posts 119 karma points
    May 03, 2016 @ 12:13
    GShenanigan
    0

    I've managed to find a solution that works fairly well for the issue I was experiencing:

    • Calling my.domain/umbraco/dialogs/republish.aspx?xml=true
      • This causes the XML in the umbracoContentXml table in the DB to be updated with the new values from cmsPropertyData.
    • Deleting umbraco.config
    • Restarting the App Pool
      • This causes the umbraco.config file to be recreated from the umbracoContentXml table and also destroys any in-memory cache.

    The additional step I had to take to get my content to show, was to rebuild one of my Examine Indexes. In my case, I'm using an ecommerce plugin called uWebshop, which makes use of an index named "External". Once I rebuilt this, and restarted the App Pool, the content is showing on my site.

    So maybe try rebuilding any Examine Indexes that your site may use for its content, in case those haven't rebuilt properly when your content has been changed via the DB.

  • Tony Cheetham 24 posts 134 karma points
    May 03, 2016 @ 13:23
    Tony Cheetham
    0

    Thanks for updating the thread!

    I actually tried this, but I found the xml=true actually pushes the data back into the XML!! Luckily I kept the SQL script that moved all the data around, but this process definately didn't work for me....Turns out, I was directly editing the XML in the contentXML table, when I should have been looking at the cmsPropertyData. Schoolboy error, but thought I would log it here for prosperity.

  • M N 121 posts 206 karma points
    Jul 18, 2018 @ 23:29
    M N
    0

    Ran into a similar snag, too many nodes and needed to update a single field on all of them from a CSV file. Quick and dirty functions

    1) Get the ID of the property/field you're trying to update

    public static int GetPropertyTypeID(string docTypeAlias, string fieldAlis) {
    
        IContentType contentType = ApplicationContext.Current.Services.ContentTypeService.GetContentType(docTypeAlias);
    
        PropertyType propertyType = contentType.PropertyTypes.FirstOrDefault(a => a.Alias == fieldAlis);
    
        return propertyType.Id;
    }
    

    2) Generate the SQL. - Only update the latest value. Careful though, Umbraco has several property types, and date time is in the dataDate column, this method targets text (dataNvarchar)

    public static int SQLUpdateAField(int contentNodeId, int propertyTypeId, string newValue){
        string stringSQL = 
            string.Format(
            @"UPDATE cmsPropertyData
                    SET dataNvarchar = '{0}'
                    WHERE Id IN
                    (
                        SELECT TOP 1 Id
                        FROM cmsPropertyData WHERE propertytypeid = {1} AND contentNodeId = {2}
                    ORDER BY Id DESC
                )",
            newValue, 
            propertyTypeId,
            contentNodeId);
    
        var sql = new Sql(stringSQL);
    
        var results = ApplicationContext.Current.DatabaseContext.Database.Execute(sql);
    
        return results; // number rows affected
    }
    

    3) Usage

    // get property id    
    int propertyId = GetPropertyTypeID("yourDocTypeAlias", "fieldAlias");
    
    // call with your content node id, property id, and new value
    int rowsAffected = SQLUpdateAField(123NodeID, propertyId, "NEW VALUE");
    

    although you might want to comment out the Execute() and grab the string it generates and try it in SSMS before you toss any of this in a loop :) And i'm sure you could modify and concat the SQL for a single payload.

    HTH someone cheers.

Please Sign in or register to post replies

Write your reply to:

Draft