So I have a macro and let's call this 'X' and want to delete it. Deleting it will create dangling references (therefore errors) around the website on content nodes that uses that macro X.
Therefore, I'd like to replace macro X with macro Y.
There's a lot of content nodes using this macro.
What's the quickest way to do this changeover?
Macros follow a fairly simple pattern when stored inside a Richtext editor. If you look at one of your existing nodes, you should be able to see what to look for.
Then you would need to iterate through all of your content nodes, looping through all properties using a RTE property editor - and update the values, replacing the old macro alias with the new one. Make sure all nodes where you end up actually doing a change, are republished (you can skip republishing all the nodes where the macro isn't found, to speed up the process a bit).
Apart from this - if you use your macro via pickers or something similar, you would have to fix these properties up to. The most common thing though, is to have macro used via RTEs.
Use the ContentService to work with your content nodes - I wouldn't recommend fixing this directly in the database unless you know exactly which tables to update and what needs to go where - and which indexes need to be rebuilt afterwards. Otherwise your site may end up having an invalid data state.