Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Christian Tabligan
  Where is Christian Tabligan?
 Bacolod City, Neg. Occ.
 Philippines
 Christian Tabligan
 To: Tariq Mehmood
  Where is Tariq Mehmood?
 BAHAWALPUR
 Pakistan
 Tariq Mehmood
 Tags
Subject: RE: UDATE instead REPLACE
Thread ID: 289065 Message ID: 289091 # Views: 41 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 10:08:55 AM         
   


> >
> > Not tested...
> >
> >
> > UPDATE A SET A.dr_amount = B.am1;
> > 		FROM cr_sum A JOIN;
> > 			(select vou_no, sum(iif(type="P",cr_amount,0)) - sum(iif(type="D",dr_amount,0) as am1 FROM crsalp group by 1) B;
> > 				 ON B.vou_no == A.vou_no
> > 

> >
> >
> > Best Regards,
> >
> > CriZ (,")
> >
> > "We are the foxes, we are the runners, we are the dancers, we create the dreams."
>
>
> But it updates only First record.

UPDATE A SET A.dr_amount = B.am1;
FROM (select vou_no, sum(iif(type="P",cr_amount,0)) - sum(iif(type="D",dr_amount,0) as am1 FROM crsalp group by 1) B;
JOIN cr_sum A;
ON B.vou_no == A.vou_no

[ADDED]

or just..

UPDATE cr_sum SET A.dr_amount = B.am1;
   FROM (select vou_no, sum(iif(type="P",cr_amount,0)) - sum(iif(type="D",dr_amount,0) as am1 FROM crsalp group by 1) B;
WHERE B.vou_no == cr_sum.vou_no


Best Regards,

CriZ (,")

"We are the foxes, we are the runners, we are the dancers, we create the dreams."

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