SQL 用同一表中另一列的值更新每一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13929158/
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
Update every row with a value from another column from same table
提问by Snake Eyes
I have the following example of table content
我有以下表格内容示例
+ Column1 | Column2 | Column3 +
+---------|---------|---------+
+ 1 | val.txt | +
+ 2 | test.xls| +
+ 3 | abc.dwg | +
+ 4 | y.txt | +
+---------|---------|---------+
I want to update Column3with information from Column2, which means I want to (in this case) extract the extension from Column2and put it in Column3.
我想Column3使用来自 的信息进行更新Column2，这意味着我想（在这种情况下）从中提取扩展名Column2并将其放入Column3.
I want the result to look like
我希望结果看起来像
+ Column1 | Column2 | Column3 +
+---------|---------|---------+
+ 1 | val.txt | .txt +
+ 2 | test.xls| .xls +
+ 3 | abc.dwg | .dwg +
+ 4 | y.txt | .txt +
+---------|---------|---------+
How to do that with an UPDATEstatement?
如何用UPDATE语句做到这一点？
I know how to extract the extension:
我知道如何提取扩展名：
SUBSTRING(Column2, LEN(Column2)-3, LEN(Column2)) AS Extension
回答by marc_s
How about this:
这个怎么样：
UPDATE dbo.YourTable
SET Column3 = SUBSTRING(Column2, LEN(Column2)-3, LEN(Column2))
If needed, you can also include a WHEREclause to limit the rows being updated, e.g.:
如果需要，您还可以包含一个WHERE子句来限制正在更新的行，例如：
UPDATE dbo.YourTable
SET Column3 = SUBSTRING(Column2, LEN(Column2)-3, LEN(Column2))
WHERE Column3 IS NULL
or something like that.
或类似的东西。
回答by Buzz
try this
尝试这个
UPDATE dbo.YourTable
SET Column3 =SUBSTRING(Column2,CHARINDEX('.',Column2,0),(LEN(Column2)-CHARINDEX('.',Column2,0)+1))
回答by Martin Smith
To extract the last 4 characters the simplest way is
要提取最后 4 个字符，最简单的方法是
UPDATE dbo.YourTable
SET Column3 = RIGHT(Column2,4);
To extract everything to the right of the last dot and so work correctly with file names such as Foo.designer.csyou can use
提取最后一个点右侧的所有内容，以便正确使用文件名，例如Foo.designer.cs您可以使用
UPDATE dbo.YourTable
SET Column3 = '.' + RIGHT(Column2, CHARINDEX('.', REVERSE('.' + Column2)) - 1)
