Updating one column based on the value of another column

Question

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

and so on...

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?

Answer 1

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 Vendor setup something like this:

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

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

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

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 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; 

Result:

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

SQL Fiddle Demo

Answer 2

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.