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 determine 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 = 4010 all
accno = CASE
WHEN jno < '09999' THEN '4010'
WHEN jno = '00011' THEN '4011'
WHEN jno = '00012' THEN '4012'
WHEN jno = '00014' THEN '4714'
WHEN (jno > '80000' AND jno < '99998') THEN '4018'
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 > '60000' AND jno < '80000') THEN '4015'
END
this worked