How to replace one string on multiple titles

Question

In my Microsoft SQL Server Management if I use this query:

select * from Modules where ModuleTitle like '%MyOldString%'

I can find multiple results with different ModuleIDs like 1, 2, 5, 1257, etc. So now, I want to backup this ModuleIDs, then change all the string "MyOldString" to "MyNewString" in all that Module Titles, how should I do ? If change one of them, I can use:

update Modules set ModuleTitle = 'MyNewString' where ModuleID = 1257

But now, I only need to replace the string and all at one for all that in the search results, is it possible ? And I need to backup that ModuleIDs, in case I need to change them back.

Answer 1

You could update it with a replace query:
update Modules SET ModuleTitle = REPLACE(ModuleTitle,'MyOldString','MyNewString') WHERE ModuleTitle like '%MyOldString%' ;

Answer 2

First, you can easily change the string using the same WHERE expression as you do for searching.

  select * from Modules where ModuleTitle like '%MyOldString%'

You can update using

 update Modules set ModuleTitle = 'MyNewString' 
 where ModuleTitle like '%MyOldString%'

As far as backup, where do you want to back the records up to? Just title or the full record?

One option would be to write a trigger, and automatically back things up into a separate table (containing ID, old title, and an update date). The benefit of the trigger would be the records get logged every time the title changes, not just when your code does it.

CREATE TABLE Module_backup 
(Module_ID int,
 oldtitle VARCHAR(200),
 updDate DATETIME DEFAULT getDate()
)

Add a trigger to the table. Basically, in the trigger, if both INSERTED and DELETED are populated (i.e. an update operation),

INSERT INTO Module_backup(Module_id,oldtitle)
SELECT Module_Id,ModuleTitle FROM DELETED