Any way to change urls in posts to a new url from the backend/database by an sql statement/similar
Hi Wendy,
lovely task you are outlining there. Theoretically SQL knows about a REPLACE() function to replace substrings in one call, but unfortunately that never made it to LiQL. Arguably it's not the most common activity on a community dataset :)
You would need to approach this in a script with three steps:
- Pull all matching messages and their ID via a SELECT id, body FROM messages WHERE body matches ('yourstring') call
- For each message retrieved in the 1st step perform a substring replace.
- Write the updated message back via the UPDATE function
All the needed LiQL functions and parameters are described in the API v2 documentation on the message resource. This can be done as a tool with almost any script language or also realized on platform via a Lithium custom component.
While you are at it you might consider future proofing this approach for the next url update in 3 years ;) For example you could move the urls into a new custom data field that would be easier to update. This would require then to update your message UI to show this additional link in a separate UI element.
