Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Dave Dawson
  Where is Dave Dawson?
 Casper
 Wyoming - United States
 Dave Dawson
 To: Stefan Wuebbe
  Where is Stefan Wuebbe?
 Hamburg
 Germany
 Stefan Wuebbe
 Tags
Subject: RE: DELETE with Target
Thread ID: 189218 Message ID: 189224 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, August 13, 2008 10:52:48 PM         
   


I had just tried what is listed below, and it appeared to work, but the status bar (during the browse) still says there are 21287 records. The first _TALLY message shows 1, the second message shows 374 or 21287, and the last _TALLY message shows 374, and the RECCOUNT() message says 21287 too. Maybe the last _TALLY message is showing the tally from the sub-query, and it didn't really delete any. Sometimes I feel like I'm playing a guessing game with vfp. ;-)

BTW, I get the same results with your code...

Thanks,
Dave


RECALL ALL IN 'DaveSave'

SELECT COUNT(*) ;
  FROM DaveSave ;
  WHERE AcctNumb - Step1 - Step2 - Step3 ;
    IN (;
      SELECT  AcctNumb - Step1 - Step2 - Step3 ;
	FROM c_codes_list ;
	WHERE NOT Active) ;
  INTO ARRAY aTemp

MESSAGEBOX(_TALLY)

IF MESSAGEBOX('Table has ' + TRANSFORM(aTemp) + ' inactive of ' ;
    + TRANSFORM(RECCOUNT('DaveSave')) ;
    + ' records...delete them?', 4) = 6
  DELETE FROM DaveSave ;
    WHERE AcctNumb - Step1 - Step2 - Step3 ;
      IN (;
	SELECT  AcctNumb - Step1 - Step2 - Step3 ;
	  FROM c_codes_list ;
	  WHERE NOT Active)
					
  MESSAGEBOX(_TALLY)
ENDIF

MESSAGEBOX(RECCOUNT('DaveSave'))

SELECT 'DaveSave'
BROWSE


> > Why would the delete below get rid of all the records in the DaveSave table if there are any NOT Active records in c_codes_list?
> >
> > In one dataset, there are about 320 NOT Active records out of about 21,000 and all the records are deleted from DaveSave.
> >
> > In another dataset, there are no NOT Active records, and none of the DaveSave records are deleted.
> >
> > I apparently don't understand how to use the DELETE with a target.
> >
> > The RECALL ALL is just so I can re-test, and DELETED is ON.
> >
> > Thanks,
> > Dave
> >
> >
> > RECALL ALL IN 'DaveSave'
> > 
> > SELECT COUNT(*) ;
> > 	FROM DaveSave a1 ;
> > 		JOIN c_codes_list a2 ;
> > 			ON a1.AcctNumb == a2.AcctNumb ;
> > 				AND a1.Step1 == a2.Step1 ;
> > 				AND a1.Step2 == a2.Step2 ;
> > 				AND a1.Step3 == a2.Step3 ;
> > 	WHERE NOT a2.Active ;
> > 	INTO ARRAY aTemp
> > 
> > IF MESSAGEBOX('Table has ' + TRANSFORM(aTemp) + ' of ' ;
> > 		+ TRANSFORM(RECCOUNT('DaveSave')) ;
> > 		+ ' inactive records...delete them?', 4) = 6
> > 	DELETE DaveSave ;
> > 		FROM DaveSave a1 ;
> > 			JOIN c_codes_list a2 ;
> > 				ON a1.AcctNumb == a2.AcctNumb ;
> > 					AND a1.Step1 == a2.Step1 ;
> > 					AND a1.Step2 == a2.Step2 ;
> > 					AND a1.Step3 == a2.Step3 ;
> > 		WHERE NOT a2.Active
> > ENDIF
> > 
> > SELECT 'DaveSave'
> > BROWSE
> > 

>
> Does this modified Delete statement do what you want?
>
>
> IF MESSAGEBOX('Table has ' + TRANSFORM(aTemp) + ' of ' ;
> 		+ TRANSFORM(RECCOUNT('DaveSave')) ;
> 		+ ' inactive records...delete them?', 4) = 6
> 	DELETE FROM DaveSave ;
> 		WHERE acctnumb In ( ;
> 			Select acctnumb FROM c_codes_list a2 ;
> 				WHERE a2.AcctNumb = DaveSave.AcctNumb ;
> 					AND a2.Step1 = DaveSave.Step1 ;
> 					AND a2.Step2 = DaveSave.Step2 ;
> 					AND a2.Step3 = DaveSave.Step3 ;
> 					AND NOT a2.active )
> ENDIF
> 

>
>
> hth
> -Stefan

ENTIRE THREAD

DELETE with Target Posted by Dave Dawson @ 8/13/2008 9:45:46 PM
RE: DELETE with Target Posted by Stefan Wuebbe @ 8/13/2008 10:25:34 PM
RE: DELETE with Target Posted by Dave Dawson @ 8/13/2008 10:52:48 PM
RE: DELETE with Target Posted by Dave Dawson @ 8/14/2008 1:02:50 AM
RE: DELETE with Target Posted by Tamar Granor @ 8/13/2008 10:27:36 PM
RE: DELETE with Target Posted by Dave Dawson @ 8/13/2008 10:55:46 PM