Just to add, I'm a big fan of code like the following because it enables you to select the subquery and verify ( right+click execute the selection) that the update is the one you wish to do.

UPDATE cr_Sum ;
   SET dr_Amount = JJ.CR_Amount - JJ.CR_Amount1 ;
   FROM CRSalp ;
  JOIN ( ;
      SELECT SUM(IIF(CR.Type = 'P', CR_Amount, 000000.00)) AS CR_Amount, ;
             SUM(IIF(CR.Type = 'D', CR_Amount, 000000.00)) AS CR_Amount1, ;
             CR.vou_no ;
           FROM CRSalp AS CR ;
          GROUP BY CR.vou_no ;
          )  AS JJ ON JJ.vou_no = CRSalp.vou_no 

Note the 0000.00 type of syntax. The place holder has to have the appropriate size so the column gets created properly if the first record is the 0 case.
Thanks to VFP 9!


>You can probably make this even simpler, but this should work.

> select crsalp
> goto top
>Scan
>   lnVou_no=crsalp.you_no
>   select ;
>      sum(iif(type="P",cr_amount,0))as cr_amount,;
>      sum(iif(type="D",dr_amount,0))as ddr_amount1;
>      where vou_no=lnvou_no;
>      from crsalp;
>      group by vou_no;
>      into cursor xyz readwrite
>   wait window transform(lnvou_no) at srows()/2,scols()/2 nowait
>   update cr_sum set dr_amount=xyz.cr_amount-xyz.ddr_amount1 where vou_no=lnVou_no
>Endscan
> 
>messagebox("Done",0+16+256,"Successfully")

>
>
>
>>Dear Sir,
>>
>> I have following working codes
>>
>>

>> select crsalp
>> goto top
>> do while !eof()
>> 	scatter memvar
>> 
>> 	xyz="crs"+sys(2015)
>> 	select ;
>> 		sum(iif(type="P",cr_amount,0))as cr_amount,;
>> 		sum(iif(type="D",dr_amount,0))as ddr_amount1;
>> 		where vou_no=m.vou_no;
>> 		from crsalp;
>> 		group by vou_no;
>> 		into cursor xyz readwrite
>> 
>> 	wait window alltrim(str(m.vou_no)) at srows()/2,scols()/2 nowait
>> 
>> 	select xyz
>> 	am1=cr_amount-ddr_amount1
>> 
>> 	select cr_sum
>> 	locate for vou_no=M.vou_no
>> 	if found()
>> 		replace dr_amount with am1
>> 	endif
>> 
>> 	select crsalp
>> 	if !eof()
>> 		skip
>> 	endif
>> 
>> enddo
>> 
>> messagebox("Done",0+16+256,"Successfully")
>>

>>
>>I need UPDADTE SQL command against above codes.
>>
>>Please help
>>
>>
>>