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!

*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.

Jobs

Relaional data query with replace

Relaional data query with replace

(OP)
I have two databases I am trying to replace a fields in one. I know it might look crude, just after functionality. My question is why does the statement on the very last line only replace one record that matches when the usaworking has multiple records that match the customer field?

SET SAFETY OFF
CLOSE ALL
CLEAR
SELECT 1
USE alld
INDEX ON ALLTRIM(customer) TO auto
SELECT 2
USE usworking
INDEX ON ALLTRIM(customer) TO working
SELECT 1
SET RELATION TO alltrim(customer) INTO 2
replace all b.mailer_typ with 'Dimensonial' for a.customer = b.customer <---- should replace all that match?

RE: Relaional data query with replace

Active workarea is 1 (alld), I'd say it has to do with that.
The for clause is useless, the relation already takes care of finding matches
You might want to SET SKIP TO 2, too, to have a 1:n relation.

Besides, a simple warning: Even when both indexes are defined on ALLTRIM(customer) with EXACT OFF the relation will also make inexact matches. VFP will extend the ALLTRIM() index to the field width anyway, you don't make the index smaller. A good strategy to make indexes compatible and exact is to PADR them to same common biggest length.

Bye, Olaf.

RE: Relaional data query with replace

I just tested this case:

CODE

CREATE CURSOR alld (customer C(15))
CREATE CURSOR usworking (customer C(20), mailer_typ C(20) DEFAULT "none")

* Notice: different size customer fields are intentional to demonstrate the matching difficulty
*         In a good design same data should be in same type and same size fields.

INSERT INTO alld VALUES ("ZYX")
INSERT INTO alld VALUES ("ABC")

INSERT INTO usworking (customer) VALUES ("DEF")
INSERT INTO usworking (customer) VALUES ("ABC")
INSERT INTO usworking (customer) VALUES ("ABC")
INSERT INTO usworking (customer) VALUES ("DEF")

SELECT alld
INDEX ON PADR(customer,20) TO auto
SELECT usworking
INDEX ON PADR(customer,20) TO working

SELECT alld
SET RELATION TO PADR(customer,20) INTO usworking
SET SKIP TO usworking

REPLACE ALL usworking.mailer_typ WITH "Dimensional" 

Without SET SKIP only the first "ABC" in usworking is replaced.

You want to replace in all matches, also multiple matches, then you have to SET SKIP TO 2 additional to SET RELATION

Or do it the SQL way:

CODE

Update usworking SET mailer_typ = "Dimensional" FROM alld WHERE PADR(alld.customer,20)==PADR(usworking.customer,20) 

Bye, Olaf.

RE: Relaional data query with replace

(OP)
Thanks!! I will work with this. Helps a lot.

RE: Relaional data query with replace

(OP)
Thanks again! I did try to figure out a way to use SQL. Not my strong suit. I found you have to use SQL a lot to really be proficient with it. I'll give that a shot for practice. smile

RE: Relaional data query with replace

It would be worth your while to work out how to do this sort of thing in SQL because it is usually much more compact than the raw VFP equivalent. You're right that you need to use it a lot to be proficient, but you will soon get up to speed.

On another point, you probably don't need those INDEX ON statements - unless you are only ever going to run this code once. Indexes are persistent. Once you have created them, they stay in force until you explicitly delete them. And they automatically get updated when you update the table. Re-creating indexes each time is a waste of time.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Relaional data query with replace

Quote:

On another point, you probably don't need those INDEX ON statements - unless you are only ever going to run this code once. Indexes are persistent. Once you have created them, they stay in force until you explicitly delete them. And they automatically get updated when you update the table. Re-creating indexes each time is a waste of time.

Well, in this case he used INDEX ... TO <filename> so no, they're not persistent. He's creating old fashioned IDX files.

RE: Relaional data query with replace

Quote:

they're not persistent. He's creating old fashioned IDX files

In that case, I'll amend my advice. Don't create old-fashioned IDX files - unless you've got some good reason to.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Relaional data query with replace

Quote (gfrlaser1)

I found you have to use SQL a lot to really be proficient with it. I'll give that a shot for practice.
Not a bad idea. SQL is undoubtedly the more common way of acting on data and learning it will help with getting into other databases.

But relations perfectly work, you just have to know about the details, e.g., how they become 1 to many relations with SET SKIP. That's nothing new; it's still the legacy way SET RELATION between workarea always worked.

And then also only an index in the target workarea is necessary (the workarea specified after INTO), an index on the source workarea which you directly skip through with a REPLACE ALL and not just indirectly via the relation is just changing the order in which records of the parent workarea are processed, when you want to process all records, there is no advantage in indexing, it just takes extra time to index.

Dan also already mentioned the difference of temporary IDX files you create and permanent index tags, which would go into a CDX file. If you have such files, you should look for an index tag that already exists and use it, that also saves time. Permanent indexes have many more advantages, you only care about their definition once, and they are updated no matter how the DBF/FPT files are updated via VFP core runtime or ODBC driver or OLEDB Provider (which both have the parts of that VFP core runtime related to handling data)

Many topics overlap anyway, correct indexing is helpful for both legacy and SQL ways of acting on data.

As long as DBFs were used I continuously used relations and browse windows for a customer to maintain data in more than just two level hierarchies of data. You can easily connect more than two tables enable browsing data and also changing it, even in normalized databases. That only changed when going for MSSQL as database backend.

Bye, Olaf.

RE: Relaional data query with replace

(OP)
Worked perfect!!

RE: Relaional data query with replace

Hi,

If you prefer the NON-SQL and the NON-RELATIONAL way you could code something like this

CODE -->

LOCAL lcCustomer
...
USE alld IN 0 && that is a zero
USE usworking IN 0
SELECT alld
SCAN
lcCustomer = alld.Customer
REPLACE IN usworking ALL mailer_typ WITH 'Dimensonial' FOR usworking.customer = lcCustomer
ENDSCAN ...

You may also want to check the USE command and the REPLACE command

hth

MK

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!

Resources

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