Relaional data query with replace 5

[gfrlaser1]

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?

 

[Olaf Doschke]

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.

 

[Olaf Doschke]

I just tested this case:

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:

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

Bye, Olaf.

 

[gfrlaser1]

Thanks!! I will work with this. Helps a lot.

 

[gfrlaser1]

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.

 

[Mike Lewis]

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

 

[Dan Freeman]

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.

 

[Mike Lewis]

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

 

[Olaf Doschke]

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.

 

[gfrlaser1]

Worked perfect!!

 

[mjcmkrsr]

Hi,

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

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

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

hth

MK

 

[TamarGranor]

I just added this paper to my website last week; it would probably help you get going with SQL:

http://tomorrowssolutionsllc.com/ConferenceSessions/Learn to use SQL.PDF

Tamar