locked
how to populate a table's fields with multiple values depending on the row RRS feed

  • Question

  • I have a table called sinvent with columns accno, jno, and saccno. jno and saccno are already filled with numbers, alot of the numbers repeat.i need to populate accno with the following values based on which jno its associated with, but some of the ranges overlap. for example I first need to fill the entire accno column with '4010' then the following criteria deternine what to replace the values with i have tried the code below and it does not preform all the tasks, only some of them. how do I write a single query to do this?

    UPDATE sinvent
    accno = CASE 
    WHEN jno = '00011' THEN '4011'
    WHEN jno = '00012' THEN '4012' 
    WHEN jno = '00014' THEN '4714' 
    WHEN (saccno = '7' OR saccno = '8') THEN '4008' 
    WHEN (jno = '4714' OR jno = '04714') THEN '4714'
    WHEN (jno = '4012' OR jno = '04012') THEN '4012'
    WHEN (jno = '4006' OR jno = '4506') THEN '4006'
    WHEN jno = '4116' THEN '4116'
    WHEN jno = '04011' THEN '4011'
    WHEN (jno > '80000' AND jno < '99998') THEN '4018' 
    WHEN (jno > '60000' AND jno < '80000') THEN '4015'
    WHEN jno < '09999' THEN '4010'
    END

    Thursday, June 11, 2015 3:40 PM

Answers

  • Hi schatcher94,

    The sequence of the WHENs does affect the update result, the leading ones take affect when there's overlap with the one's latter. You may have to rearrange the sequence to get the expected result. Please post some sample data and the expected output.

    DECLARE @T TABLE(col1 INT,col2 INT,UPDATE_COLUMN VARCHAR(99))
    INSERT INTO @T(col1,col2) VALUES(1,2),(2,1)
    SELECT * FROM @T
    
    UPDATE @T
    SET UPDATE_COLUMN =CASE
    WHEN col1=1 THEN 'col1=1' --col1=1 and col2=2 indicates the same row, so the WHEN col2=2 won't afftect
    WHEN col2=2 THEN 'col2=2'
    WHEN col1=2 THEN 'col1=2'
    END;
    SELECT * FROM @T
    
    UPDATE @T
    SET UPDATE_COLUMN =CASE
    WHEN col2=2 THEN 'col2=2' --col2=2 and col1=1 indicates the same row, so the WHEN col1=1 won't affect
    WHEN col1=1 THEN 'col1=1'
    WHEN col1=2 THEN 'col1=2'
    END;
    SELECT * FROM @T

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    Friday, June 12, 2015 3:09 AM
  • Hi shatcher94,

    Could you please post more detailed sample data and expected output?
    Wednesday, June 17, 2015 1:08 AM

All replies

  • Hi schatcher94,

    The sequence of the WHENs does affect the update result, the leading ones take affect when there's overlap with the one's latter. You may have to rearrange the sequence to get the expected result. Please post some sample data and the expected output.

    DECLARE @T TABLE(col1 INT,col2 INT,UPDATE_COLUMN VARCHAR(99))
    INSERT INTO @T(col1,col2) VALUES(1,2),(2,1)
    SELECT * FROM @T
    
    UPDATE @T
    SET UPDATE_COLUMN =CASE
    WHEN col1=1 THEN 'col1=1' --col1=1 and col2=2 indicates the same row, so the WHEN col2=2 won't afftect
    WHEN col2=2 THEN 'col2=2'
    WHEN col1=2 THEN 'col1=2'
    END;
    SELECT * FROM @T
    
    UPDATE @T
    SET UPDATE_COLUMN =CASE
    WHEN col2=2 THEN 'col2=2' --col2=2 and col1=1 indicates the same row, so the WHEN col1=1 won't affect
    WHEN col1=1 THEN 'col1=1'
    WHEN col1=2 THEN 'col1=2'
    END;
    SELECT * FROM @T

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    Friday, June 12, 2015 3:09 AM
  • the jno are all the ones listed in that list and some random ones that aren't that should be picked up by the accno = 4010 for all. and the jobs with saccno 7 and 8 are jno 00012
    Friday, June 12, 2015 12:56 PM
  • Hi shatcher94,

    Could you please post more detailed sample data and expected output?
    Wednesday, June 17, 2015 1:08 AM