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?
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 |
+--------------+-----------------+