I need to make a lot of changes in an old database. I do have the old and new values in an excel table. Can I do anything in SQL to find the values from the "old" cell (from Excel) and replace with the values from "new" cell?
1 Answer
Lets say new values are in column A, old values are in column B.
Then put a formula of the following kind in column C, first row:
="UPDATE mytable set mycolumn='"& A1 & "' WHERE mycolumn='" & B1 & "';"
(and fill out all rows). You need to adapt it to your situation, but I hope you get the idea.
Finally, copy-paste the result into a text file and pass it over to the SQL command line tool of your database, or whatever SQL interface you are going to use.
You could also create some VBA macro which connects to your DB and executes those SQLs. In that case, you may consider not to use formulas to create the Update SQL, but VBA string manipulation directly.
For replacing text (like links) inside a text column, the generated SQL needs to look different. You did not mention the DBMS you are using, syntax differs between systems. For example, when using MSSQL, according to the docs, the generated SQLs should look something like this:
UPDATE mytable SET mycolumn=REPLACE(mycolumn,'oldvalue','newvalue')
Beware, the order of execution of those values can matter here, if one replacement generates a new string which becomes subject to a replacement applied later.
