Re: SQL Update Statement

Thanks Rob,

I think I'm almost there, that looks like it would do it and makes sense to me 
but my system (V5R3) yelled at me saying

Token , was not valid. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> 

And positioned the cursor on the , in (tbluserpt.obdiv, tbluserpt.obcls)

Tim



update tbluserpt                                                   
set tbluserpt.oboprce=                                             
    (select tbluserpt.ifoth / (1 - tblusrp.okmark)                 
     from  tblusrp                                                 
     where (tbluserpt.obdiv=tblusrp.obdiv and                      
            tbluserpt.obcls=tblusrp.obcls))                        
where tbluserpt.oboprce = 0                                        
  and (tbluserpt.obdiv, tbluserpt.obcls) in                        
      (select tbluserpt.obdiv, tbluserpt.obcls                     
       from tbluserpt inner join tblusrp                           
       using (obdiv, obcls))                                       

subject: Re: SQL Update Statement


select * from tbluserpt 
1....+....2....+....3....+....4....+....5....+.
   OBOPRCE                 IFOTH   OBDIV  OBCLS
  15.50000              15.50000     3      3 
    .00000              15.50000     1      1 
    .00000              15.50000     2      2 

select * from tblusrp 
....+....1....+....2....+....3....
OBDIV  OBCLS               OKMARK 
  1      1                3.00000 
  3      3                4.00000 

update tbluserpt 
set tbluserpt.oboprce= 
    (select tbluserpt.ifoth / (1 - tblusrp.okmark)
     from  tblusrp 
     where (tbluserpt.obdiv=tblusrp.obdiv and 
            tbluserpt.obcls=tblusrp.obcls)) 
where tbluserpt.oboprce = 0 
  and (tbluserpt.obdiv, tbluserpt.obcls) in 
      (select tbluserpt.obdiv, tbluserpt.obcls 
       from tbluserpt inner join tblusrp 
       using (obdiv, obcls)) 
1 rows updated in TBLUSERPT in QTEMP. 

select * from tbluserpt 
1....+....2....+....3....+....4....+....5....+.
   OBOPRCE                 IFOTH   OBDIV  OBCLS
  15.50000              15.50000     3      3 
   7.75000-             15.50000     1      1 
    .00000              15.50000     2      2 

Rob Berendt