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

In the case you have many values to update into the target table, you could use a merge statement (from the doc here).

Building from the nice examples above :

-- (re)create the example tables
drop table if exists TEST.dbo.Vendor
drop table if exists TEST.dbo.refVendor

create table TEST.dbo.Vendor (AccountTerms int, ulARAgeing varchar(50), amount int); -- constraint PK_vendor primary key (AccountTerms, ulARAgeing) );
create table TEST.dbo.refVendor (AccountTerms int, ulARAgeing varchar(50) constraint PK_refVendor primary key (AccountTerms, ulARAgeing) );

-- insert values
insert into TEST.dbo.Vendor values
(0,'Test', 100),
(0,'Test', 150),
(1,'Test1', 50),
(1,'Test1', 55),
(1,'Test1', 53),
(2,'Test2', 40),
(2,'Test2', 60);

insert into TEST.dbo.refVendor values
(0,'30 days'),
(1,'40 days'),
(2,'50 days');

--
select * from TEST.dbo.Vendor
select * from TEST.dbo.refVendor


-- update based on match or insert missing values if needed
merge into TEST.dbo.Vendor t -- target
    using TEST.dbo.refVendor s -- source
        on t.AccountTerms = s.AccountTerms
    when matched then
        update set ulARAgeing = s.ulARAgeing
    when not matched then
        insert (AccountTerms, ulARAgeing) values (s.AccountTerms, s.ulARAgeing)
;

select * from TEST.dbo.Vendor

Results

-- base table Vendor
AccountTerms    ulARAgeing  amount
0   Test    100
0   Test    150
1   Test1   50
1   Test1   55
1   Test1   53
2   Test2   40
2   Test2   60


-- base table refVendor
AccountTerms    ulARAgeing
0   30 days
1   40 days
2   50 days


-- result table Vendor
AccountTerms    ulARAgeing  amount
0   30 days 100
0   30 days 150
1   40 days 50
1   40 days 55
1   40 days 53
2   50 days 40
2   50 days 60

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.

Answer 3

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