SQL 用新值替换旧值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1629201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL replace old values with new ones
提问by elhombre
I have a table named tbl.Products, which has a column named articlenumberand is full of numbers like s401, s402, etc.
我有一个名为表tbl.Products，其中有一个名为列articlenumber，并充满了相同的数字的s401，s402等等。
I generated a list with new article numbers which will replace the old ones:
我生成了一个带有新文章编号的列表，它将替换旧的文章编号：
s401 I00010
s402 I00020
s403 I00030
s403 I00040
...
I have a query from which I hoped that it would work out, but somehow it does nothing.
我有一个查询，我希望它能解决问题，但不知何故它什么也没做。
(of course I have nested the other values into the query)
（当然，我已将其他值嵌套到查询中）
SELECT REPLACE('articlenumber','s401','I00010') FROM tbl.Products
How do I get a query which replaces old values with new ones in a column like this?
我如何获得一个查询，在这样的列中用新值替换旧值？
回答by Thorsten
Doing a replace can have problems (what if you have an articles s401 and s4010?), therefore it'll be more robust to do it like this:
进行替换可能会出现问题（如果您有文章 s401 和 s4010 怎么办？），因此这样做会更健壮：
Update tblProducts
SET articlenumber = 'I000010'
Where articlenumber = 's401';
If you have a number of changes to do, you can either generate a little script (for example with Excel, as TheButcher suggested), or you could import the data into a little table tblVals with the columns oldVal and newVal and then use this statement:
如果您有许多更改要做，您可以生成一个小脚本（例如使用 Excel，正如 TheButcher 建议的那样），或者您可以将数据导入到一个包含 oldVal 和 newVal 列的小表 tblVals 中，然后使用此语句：
Update tblProducts p
SET articlenumber = (Select newVal
From tblVals
where oldVal = p.articlenumber);
This allows you to do the update in one statement which again will be more robust than running a script which may run into problems if it is really long.
这允许您在一个语句中进行更新，这再次比运行可能会遇到问题的脚本更健壮，如果它真的很长。
A third idea would be to do the logic in constructing the new number for the old number (if such a thing exists) in SQL (or a Stored Procedure) like this:
第三个想法是在 SQL（或存储过程）中为旧数字（如果存在这样的东西）构建新数字的逻辑如下：
Update tblProducts p
SET articlenumber = 'I0000' || Right(articlenumber,1) || '0'
(Of course this is totally simplified and probably not nearly sufficient for your 30k rows).
（当然，这是完全简化的，对于您的 30k 行来说可能还不够）。
回答by The_Butcher
You are just selecting the newly replaced values and not doing anything with them... thats a very good idea when using replace, always select first to double check that you will get the expected result :)
您只是选择了新替换的值而不对它们做任何事情......使用替换时这是一个很好的主意，总是先选择以仔细检查您是否会得到预期的结果:)
The update code -
更新代码 -
Update tbl.Products
Set articlenumber = replace(articlenumber, 's401', 'I00010')
