Reputation: 123
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?
Upvotes: 12
Views: 61193
Reputation: 1763
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
Upvotes: 0
Reputation: 2022
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.
Upvotes: 5
Reputation: 13763
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 |
+--------------+-----------------+
Upvotes: 24