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

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. how do i write a single query to do this?

accno = 4010 for jno < 09999
accno = 4011 for jno = 00011
accno = 4012 for jno = 00012
accno = 4714 for jno = 00014
accno = 4018 for jno > 80000 and jno < 99998
accno = 4008 for saccno = 7 and 8
accno = 4714 for jno = 04714 and 4714

any help would be greatly appreciated.

ive tried to use

update sinvent
set 
  accno = '4010' where jno < '09999',
  accno = '4011' where jno = '00011'

but it says a column cannot be assigned more than one value in the same clause

Answer 1

You have to use CASE inside the UPDATE statement:

UPDATE sinvent
    SET accno = CASE
        WHEN jno < '09999' THEN '4010' 
        WHEN jno = '00011' THEN '4011'
        WHEN jno = '00012' THEN '4012' 
        WHEN jno = '00014' THEN '4014' 
        WHEN (jno > '80000' and jno < '99998') THEN '4018' 
        WHEN (saccno = '7' OR saccno = '8') THEN '4008' 
        WHEN (jno = '4714' OR jno = '04714') THEN '4714' 
    END