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


> 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 a1.acctnumb 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 ;
					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