Solved

Deleting records from 2 tables vfp

Posted on 2013-05-23
5
473 Views
Last Modified: 2013-05-23
Hi:
I am running a capacity planning database. This copies all relevant records from oracle and stores them in a table called capacity. The copy routine is run several times a day, and the capacity is seen to rise as orders are added to oracle. I can report that at say 8am there were 5000 orders and at 5pm there were 5500 but have been challenged to provide details.
My solution is to create a copy of the capacity table (called lastcap) before the update, and again after (called thiscap). What I need to do is 'remove'  those records that are in both tables (or read only those that are in the later table). Does anybody know the best way of achieving this?
Thanks!
0
Comment
Question by:ClaytonGlass
5 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 250 total points
ID: 39191063
Each table record should have its own unique ID, e.g OrderID. This is the only requirement for the following query:

SELECT * FROM Capacity
WHERE OrderID NOT IN (SELECT OrderID FROM LastCap)

5000 orders isn't so many so it will be fast even without indexes.
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 39191065
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2)
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39191077
If you don't have unique ID then you have to use some time stamp but you have to ensure timestamp values can do the job, e.g. two orders having same time stamp but just one of them exported the first time will produce incorrect result.

SELECT * FROM Capacity
WHERE TimeStamp > (SELECT MAX(TimeStamp) FROM LastCap)
0
 

Author Closing Comment

by:ClaytonGlass
ID: 39191095
Thanks a lot for very prompt and effective answers! I asked a question about python 2 months ago and am still waiting! Thanks to Captain Cyril, too.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39192322
What makes me think is, you say you compare lastcap and thiscap, two copies of the capcity table before and after update.

But there is an update routine (in oracle?) which does merge oracle data into capacity.dbf, so at that stage it's already known, which records already were in capacity.dbf and which have to be added.

So I'd say the solution to your problem is already solved and you redo this by the comparison. Even if it's not much to do, when this raises at 500 records per 3 hours, you sooner or later will see a slow down, just even making the copies before and after merges will take longer and longer.

What I would question is, if the only difference is added records, because then you'd only need to store the reccount before and after the data merging, and will know how many records are new in the DBF. And as new records always are appended at the end of a dbf file, you'd then know the new records are from oldreccount+1 to EOF.

And that doesn't need two DBFs to compare.

Bye, Olaf.

PS: If this wasn't oracle, but T-SQL and the merging would be done inside SQL Server, I'd have an idea to both merge data and output the records that changed or were added as new orders with the OUTPUT clause of a MERGE Statement (an SQL command VFP does not have, though, and I don't know about oracle).

And there are even better options about logging changes via triggers and then export that changelog table. Transaction log also is one source of such change data, even without any triggers. Again I only know for SQL Server you can make use of DBCC LOG.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
There are many Password Managers (PM) out there to choose from. PM's can help with your password habits and routines, but they should not be a crutch you rely on too heavily. I also have an article for company/enterprise PM's.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now