1

Please see the pseudocode below. Basically what I'm trying to do is write a statement that when an account number contains one of the specified codes it will assign that Level to the entire account not to just that one row.

I've tried using a case statement in a CTE and few other options and I'm not having much luck. Any help is appreciated.

if AcctNo contains Code = '1' 
then level = '1'
elif AcctNo contains Code = '2'
then level = '2'


AcctNo  Code  charge   level
1       2       50     Null
1       17      60     Null
1       28      61     Null
1       36      62     Null
1       18      63     Null

Desired Results

 AcctNo  Code  charge    level
    1       2       50     2
    1       17      60     2
    1       28      61     2
    1       36      62     2
    1       18      63     2
  • 2
    Hi, Welcome to the site, please post actual code instead of pseudo-code. Table definitions, sample data and expected output would help too. – Tom V Feb 2 '16 at 21:16
  • 1
    And what if there are two rows, one with code=1 and another with code=2 (and many other rows)? – ypercubeᵀᴹ Feb 2 '16 at 21:30
3

At a really basic level it would look like this

UPDATE TableName
SET [level] = CASE WHEN AcctNo = '1' THEN '1'
                    WHEN AcctNo = '2' THEN '2'
                    ELSE 'DefaultVal' END

or

UPDATE TableName
SET [level] = CASE AcctNo WHEN '1' THEN '1'
                    WHEN '2' THEN '2'
                    ELSE 'DefaultVal' END

Depending on if you only need the one column in your conditions or if it's more complicated than that.

Edit On looking at your expected data it looks like what you want is this:

WITH Levels AS (SELECT AcctNo, Level, 
        MIN(CASE WHEN code IN (1,2,3,4,5) THEN code ELSE NULL END) 
                OVER (PARTITION BY AcctNo) AS MinCode
        FROM Test)
UPDATE Levels
SET level = MinCode

Note: This assumes that you want the minimum code for a given AcctNo.

If you just want a select statement to test then change it to this:

WITH Levels AS (SELECT AcctNo, Code, Charge, Level, 
        MIN(CASE WHEN code IN (1,2,3,4,5) THEN code ELSE NULL END) 
                OVER (PARTITION BY AcctNo) AS MinCode
        FROM Test)
SELECT * FROM Levels
| improve this answer | |
  • The list of codes that relate to a level are specifically 1-5. The rest of the codes are ancillary – Aqueouss Feb 2 '16 at 22:00
  • @KennethFisher thank you very much your solution got me my desired result, now I just need to get it so i can easily update it. – Aqueouss Feb 2 '16 at 22:42
3

I don't think you mean contains
I think you mean if any code has that value
I think this is what you are looking for

update t2  
set t2.level = t1.code 
from table t1 
join table t2 
      on t2.AcctNo = t1.AcctNo 
     and             t1.Code in (1,2) 

If an AcctNo had Code both 1 and 2 then this would be non-deterministic

If you need one (actually 2) to win then

update t2  
set t2.level = t1.code 
from table t1 
join table t2 
      on t2.AcctNo = t1.AcctNo 
     and             t1.Code in (1);
update t2  
set t2.level = t1.code 
from table t1 
join table t2 
      on t2.AcctNo = t1.AcctNo 
     and             t1.Code in (2);
| improve this answer | |
  • Frisbee i'm not sure on the proper operator, that is where my problem stems from. What I mean is if a certain account number has a code related to it in any of the different row than that set code should be the level. – Aqueouss Feb 2 '16 at 21:37
  • The data set i listed is a extremely dumbed down version of what i'm looking for. I do use tables, however i'm using several CTEs and several tables in my query right now to get the information I need. – Aqueouss Feb 2 '16 at 21:57
  • 4
    Answer is only as good as the question – paparazzo Feb 2 '16 at 21:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.