MySQL issue: finding a string and replacing content

Development
1

Due to an error, a resource was created about a year ago and didn’t trigger the event that generates our unique identifiers (UID) when duplicating resources rather than creating new ones. So it also copied the UID that is used in the resource’s link_attributes field. We didn’t catch this until recently and I’m trying to update all the other resources that reference it at once from the database level but running into a problem. Or maybe there’s an easier way to do this?

I’m trying to query the database to show all resources that reference that UID in other template variables, search for the UID of the old resource and do a find & replace on it to reset any instance of that with that old UID and the new UID separated by the double pipes. This is what I have so far but I’m unsure how to target these results with a find/replace.

SELECT

    ne.id AS id,

    ne.pagetitle AS pagetitle,

    ne.content AS content,

    ne.template AS template,

    neeventdate.value AS eventstart,

    SUBSTRING(mainservice.value, 1, 2000) service_ids,

    (

        SELECT

            GROUP_CONCAT(pagetitle)

        FROM

            modx_site_content services_table

        WHERE

            template IN(12, 28) AND LOCATE(link_attributes, service_ids)

    ) all_services

FROM

    modx_site_content ne

   

LEFT JOIN modx_site_tmplvar_contentvalues neeventdate ON

    neeventdate.contentid = ne.id AND neeventdate.tmplvarid = 54

   

LEFT JOIN modx_site_tmplvar_contentvalues mainservice ON

    mainservice.contentid = ne.id AND mainservice.tmplvarid = 38

WHERE

    ne.deleted = 0 AND ne.published = 1 AND ne.template IN(34)

   

HAVING

    service_ids LIKE '%old-ID-String-Here%'

ORDER BY

    eventstart

DESC

The part that matters is the column that gets labeled service_ids.

Any help would be appreciated. thanks!

2

I’ve also tried editing right in the table like this:

UPDATE modx_site_tmplvar_contentvalues

SET value = REPLACE (value, 'OldID-Value', 'OldID-Value||NewID-Value')

WHERE tmplvarid IN (38) AND VALUE LIKE '%OldID-Value%'
3

This UPDATE statement looks ok.

If that doesn’t work then please explain more precisely what the format of this TV 38 is and what you are trying to replace.

What’s the SUBSTRING for?

4

That was to build the list of services. But I just figured out what the problem was.

All of the results were coming back with uppercase values and I didn’t realize that it was case sensitive. I added an UPPER() surrounding the old and new values in the replace() line and that worked perfectly.