×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

linking databases

linking databases

linking databases

(OP)
Need help linking common fields in two different databsaes to delete old information that is no longer useable.

I want to delete information by year, however only one database includes date/year. The only common link between databases is the order number field.

Any information would be helpful.

Thanks

RE: linking databases

One way is to have an index on the order number of the one table, I'll call it orders.  Then do a scan of the table containing the dates, I'll call it datefile.  Here's a rudimetary example:

SET DELETED ON  &&... ignore records marked for deletion
SELECT 0
USE orders ORDER ordernum
SELECT 0
USE datefile

SCAN FOR date <= date_to_delete
   SELECT orders
   DO WHILE SEEK(datefile.ordernum)
      DELETE
   ENDDO
   SELECT datefile
ENDSCAN

Dave S.

RE: linking databases

cphoto,

Another way could be establishing a relation between the two tables:

USE datefile ORDER ordernum
SELECT 0
USE orders
SET RELATION TO ordernum INTO datefile
DELETE ALL FOR YEAR(datefile.date) = year_to_delete

David.

RE: linking databases

Both suggestions above would work if you only wanted to remove records from one of the data bases, however the way I read your question is you need records deleted from both databases.

SET DELETED ON  &&... ignore records marked for deletion
SELECT 0
USE orders ORDER ordernum
SELECT 0
USE datefile

SCAN FOR date <= date_to_delete
   SELECT orders
    DO WHILE SEEK(datefile.ordernum)
         DELETE
    ENDDO
    SELECT datefile
DELETE
ENDSCAN

or

USE datefile ORDER ordernum
SELECT 0
USE orders
SET RELATION TO ordernum INTO datefile
DELETE ALL FOR YEAR(datefile.date) = year_to_delete

    && maybe <= would be better then =
SET RELATION TO
SELECT datefile
DELETE ALL FOR YEAR(datefile.date) <= year_to_delete

David W. Grewe
Dave@internationalbid.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close