Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Tariq Mehmood
  Where is Tariq Mehmood?
 BAHAWALPUR
 Pakistan
 Tariq Mehmood
 Tags
Subject: RE: UDATE instead REPLACE
Thread ID: 289065 Message ID: 289232 # Views: 32 # Ratings: 1
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, December 29, 2010 5:59:54 PM         
   


> >
> >
*********
> > CREATE CURSOR crsums (vou_no int, crsum int)
> > INSERT INTO Crsums VALUES (1, 0)
> > INSERT INTO Crsums VALUES (2, -10)
> > INSERT INTO Crsums VALUES (3, 50)
> > 
> > CREATE CURSOR Crsalp (vou_no int, type C(1), cr_amount int, dr_amount int)
> > INSERT into Crsalp VALUES (1, 'P', 10, 0 )
> > INSERT into Crsalp VALUES (1, 'P', 20, 0 )
> > INSERT into Crsalp VALUES (1, 'D', 0, 25 )
> > INSERT into Crsalp VALUES (2, 'D', 0, 10 )
> > INSERT into Crsalp VALUES (2, 'D', 0, 10 )
> > INSERT into Crsalp VALUES (3, 'P', 10, 0 )
> > INSERT into Crsalp VALUES (3, 'D', 0, 15 )
> > INSERT into Crsalp VALUES (3, 'P', 10, 0 )
> > 
> > UPDATE Crsums SET Crsum = crsum + (SELECT SUM(cr_amount)-SUM(dr_amount) from Crsalp ;
> >  WHERE vou_no=Crsums.vou_no )
> > 

> > Result:

> > Vou_no Crsum
> > 1 5
> > 2 -30
> > 3 55
> >

> >
> > I'm assuming that when there's a dr_amount then cr_amount=0, and viceversa so there's no point in checking for P and D.
> >
> > -Anders
>
>
> Dear Anders
>
> I have these cursor
>
>
> CREATE CURSOR crsalp (vou_no n(4),type c(1),cr_amount n(4))
> INSERT INTO crsalp values(1,'P',500)
> INSERT INTO crsalp values(1,'D',100)
> INSERT INTO crsalp values(2,'P',600)
> INSERT INTO crsalp values(3,'P',800)
>  
> CREATE CURSOR cr_sum (vou_no n(4),dr_amount n(4))
> INSERT INTO cr_sum values(1,0)
> INSERT INTO cr_sum values(2,0)
> INSERT INTO cr_sum values(3,0)
> 




Hello Tariq,

UPDATE Crsums SET Crsum = crsum + (select SUM(IIF(type='P',cr_amount,0))-SUM(IIF(type='D',cr_amount,0)) from Crsalp ;
 WHERE vou_no=Crsums.vou_no ) 


-Anders

ENTIRE THREAD

UDATE instead REPLACE Posted by Tariq mehmood @ 12/28/2010 8:21:00 AM
RE: UDATE instead REPLACE Posted by Christian Tabligan @ 12/28/2010 8:55:55 AM
RE: UDATE instead REPLACE Posted by Tariq mehmood @ 12/28/2010 9:23:56 AM
RE: UDATE instead REPLACE Posted by Christian Tabligan @ 12/28/2010 10:08:55 AM
RE: UDATE instead REPLACE Posted by Tariq mehmood @ 12/28/2010 6:45:05 PM
RE: UDATE instead REPLACE Posted by Christian Tabligan @ 12/29/2010 2:20:32 AM
RE: UDATE instead REPLACE Posted by Tariq mehmood @ 12/29/2010 3:31:28 AM
RE: UDATE instead REPLACE Posted by Anders Altberg @ 12/28/2010 7:34:37 PM
RE: UDATE instead REPLACE Posted by Tariq mehmood @ 12/28/2010 7:58:04 PM
RE: UDATE instead REPLACE Posted by Anders Altberg @ 12/29/2010 5:32:54 PM
RE: UDATE instead REPLACE Posted by Anders Altberg @ 12/29/2010 5:59:54 PM