根据另一列的值更新一列 [英] Updating one column based on the value of another column

问题描述

我有一个名为 Vendor 的表，在这个表中有一个名为 AccountTerms 的列，它只显示一个值(即 0、1、2、3)等等.我还有一个要使用的列 (ulARAgeing) 以反映该值的含义，例如:

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:

0: Current
1: 30 Days
2: 60 Days

等等……

我需要的是一个脚本，它会查看 AccountTerms 中的值，然后更新 ulARAgeing 以显示上面显示的单词值.我该怎么做?

What I need is a script that will look at the value in AccountTerms and will then update ulARAgeing to show the word value shown above. How do I do this?

推荐答案

我将尽量用简单的方式解释这个问题，以便于理解:

I am going to try to explain this in a simple manner as much as possible so it's easy to understand :

让我们假设，您有一个表 Vendor 设置如下:

Let's assume, you have a table Vendor setup something like this:

create table Vendor (AccountTerms int, ulARAgeing varchar(50));

然后，我们将为 Vendor 表中的两列插入一些示例值:

And, then we will insert some sample values for both columns in Vendor table:

insert into Vendor values
(0,'Test'),
(1,'Test1'),
(2,'Test2');

接下来，我们将编写一个更新语句，根据同一个表中 AccountTerms 列中的值更新您的 ulARAgeing 列:

Next, we will write an update statement to update your ulARAgeing column based on the values in AccountTerms column in the same table:

update vendor 
set ulARAgeing = (CASE 
                      WHEN AccountTerms = 0 
                        THEN 'Current'
                      WHEN AccountTerms = 1
                        THEN '30 Days'
                      WHEN AccountTerms = 2
                        THEN '60 Days'
                    END);

CASE WHEN 类似于在大多数其他编程语言中使用 IF..ELSE 语句.因此，这里我们将根据 case when 语句中的条件将现有的 ulARAgeing 值更新为不同的字符串值.所以，例如如果 AccountTerms = 0 那么我们会将 ulARAgeing 的值更新为Current"等等.

CASE WHEN is similar to using IF..ELSE statement in most other programming languages. So, here we will be updating the existing ulARAgeing value to different string value based on the condition in the case when statement. So, for e.g. if the AccountTerms = 0 then we will update the value for ulARAgeing to `Current' and so forth.

要检查上面的语句是否正确运行，您只需要运行上面的更新语句，然后再次从表中选择:

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; 

结果:

+--------------+-----------------+
| AccountTerms |   ulARAgeing    |
+--------------+-----------------+
|            0 |         Current |
|            1 |         30 Days |
|            2 |         60 Days |
+--------------+-----------------+

SQL Fiddle 演示

这篇关于根据另一列的值更新一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！