SQL 根据另一列的值更新一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32102348/
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
Updating one column based on the value of another column
提问by 4shg85
I have a table named Vendor, within this table I have a column called AccountTerms which is shows only a value (i.e. 0, 1, 2, 3) and so on. I also have a column that I want to use (ulARAgeing) in order to reflect the meaning of that value, such as:
我有一个名为 Vendor 的表，在这个表中有一个名为 AccountTerms 的列，它只显示一个值（即 0、1、2、3）等等。我还有一列要使用 ( ulARAgeing) 来反映该值的含义，例如：
0: Current
1: 30 Days
2: 60 Days
and so on...
等等...
What I need is a script that will look at the value in AccountTerms and will then update ulARAgeingto show the word value shown above. How do I do this?
我需要的是一个脚本，它会查看 AccountTerms 中的值，然后更新ulARAgeing以显示上面显示的单词值。我该怎么做呢？
回答by FutbolFan
I am going to try to explain this in a simple manner as much as possible so it's easy to understand :
我将尝试以尽可能简单的方式解释这一点，以便于理解：
Let's assume, you have a table Vendorsetup something like this:
让我们假设，你有一个Vendor像这样的表设置：
create table Vendor (AccountTerms int, ulARAgeing varchar(50));
And, then we will insert some sample values for both columns in Vendortable:
然后，我们将为表中的两列插入一些示例值Vendor：
insert into Vendor values
(0,'Test'),
(1,'Test1'),
(2,'Test2');
Next, we will write an update statement to update your ulARAgeingcolumn based on the values in AccountTermscolumn in the same table:
接下来，我们将编写一个更新语句，ulARAgeing根据AccountTerms同一个表中列中的值更新您的列：
update vendor
set ulARAgeing = (CASE
WHEN AccountTerms = 0
THEN 'Current'
WHEN AccountTerms = 1
THEN '30 Days'
WHEN AccountTerms = 2
THEN '60 Days'
END);
CASE WHENis similar to using IF..ELSEstatement in most other programming languages. So, here we will be updating the existing ulARAgeingvalue to different string value based on the condition in the case when statement. So, for e.g. if the AccountTerms = 0then we will update the value for ulARAgeingto `Current' and so forth.
CASE WHEN类似于IF..ELSE大多数其他编程语言中的using语句。因此，在这里我们将ulARAgeing根据 case when 语句中的条件将现有值更新为不同的字符串值。所以，例如，如果AccountTerms = 0然后我们将更新值ulARAgeing到“当前”等等。
To check if the above statement worked correctly, you just need to run the update statement above and then select from the table again:
要检查上述语句是否正确工作，您只需要运行上面的更新语句，然后再次从表中选择：
select * from Vendor;
Result:
结果：
+--------------+-----------------+
| AccountTerms | ulARAgeing |
+--------------+-----------------+
| 0 | Current |
| 1 | 30 Days |
| 2 | 60 Days |
+--------------+-----------------+
回答by Andrew Paes
Assuming you want a simple script to update, then it would be like this:
假设你想要一个简单的脚本来更新，那么它会是这样的：
update
Vendor
set ulARAgeing = 'Current'
where AccountTerms = 0;
Assuming you want a script where it automatically update the column from a logic of numeric progression. Then it would be like this:
假设您需要一个脚本，它可以根据数字级数的逻辑自动更新列。那么它会是这样的：
;WITH CTE
AS (select
AccountTerms
,ulARAgeing
,CONCAT((AccountTerms * 30), ' Days') as _ulARAgeing
from
Vendor)
UPDATE CTE
SET ulARAgeing = _ulARAgeing;
If by chance the value of "ulARAgeing" come from another table, then the script using "; WITH", you must use a join to get the correct value, instead of using a logic of progression.
如果“ulARAgeing”的值偶然来自另一个表，那么脚本使用“; WITH”，您必须使用连接来获取正确的值，而不是使用渐进逻辑。
