how to populate a table's fields with multiple criteria, some of which overlap

47 views Asked by At

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
1

There are 1 answers

0
Vbasic4now On BEST ANSWER

this worked

UPDATE sinvent
accno = case
when (jno > '60000' AND jno < '80000') then '4015'
when jno = '04011' then '4011'
when jno = '4116' then '4116'
when jno = '4506' then '4006'
when jno = '4006' then '4006'
when jno = '4012' then '4012'
when jno = '04012' then '4012'
when jno = '4714' then '4714'
when jno = '04714' then '4714'
when (saccno = '7' OR saccno = '8') THEN '4008' 
when jno = '00014' then '4714'
when jno = '00012' then '4012'
when jno = '00011' then '4011'
when (jno > '80000' AND jno < '99998') then '4018'
when jno < '09999' then '4010'
when code = '1168' then '4014'
else accno
end