Replace problem 1

[fluppe689]

Dear Experts,
I have a problem with replace statement.
I have 4 tables

Table 1 = adresses key : type n(1) can be 1,2 or 3)
ref c(8)
+
zone 1
zone 2

table 2,3, and 4 key type
ref
number
+
zone 1, zone 2, zone3, ...
In table 1 the replacement can only by replaced in 1 record
in table 2,3,4 there can be more records

Can i use : select table1
replace all type with newtype for type = oldtype and replace all ref with newref for ref = oldref
select table2
replace all type with newtype for type = oldtype and replace all ref with newref for ref = oldref


or can i use replace all type with newtype and replace all ref with newref for type = oldtype and ref = oldref

wfg,

FILIP

 

[craigber]

You need to do it with four replace statements.

select table1
replace all type with newtype for type = oldtype
replace all ref with newref for ref = oldref
select table2
replace all type with newtype for type = oldtype
replace all ref with newref for ref = oldref


Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[Olaf Doschke]

It's not understandable to me how your tables are designed by what you post. From the code I see type and ref are fields, so you're not talking about field types when you write type. Is key the key of a table or a field name? If both, why then are tables related by ref and not key/foreign key pairs of fields?

To replace in more than one table at once you can relate tables by SET RELATION TO, but Craigs solution is much simpler to understand and maintain.

It seems you're doing a cascade update of the primary and foreign key "ref", which is a bad idea in itself, as the key should not be something worth changing anyway.

Regarding type it seems you're having the even worse error of not having normalised table designs or you're working on a result cursor of eg pivot or cross tab wizard. Then the question is, why not change before creating that cross/pivot table(s).

Bye, Olaf.

 

[fluppe689]

Hello Olaf

My tables are normally dbf tables.
The first table is the adress table
It exists in about 50 fields in which the key is field type and field ref.
the field type can be 1,2 or 3 (1=client,2=supplier,3=address)
the ref field is a character field of 8 positions.

the other 3 tables are related in the way that there is always the field type and ref.
these tables are contacts, documents, visits.
now I want to change for example in table adress the type form 3(=address) to 1(=client).
If i do so I have to change the other tables also.
That was the question I asked because I have to change more than 1 field in the tables.

wfg,

Filip

 

[Olaf Doschke]

yes, and that's the error, that you need to change all types, you're storing that type info redundant. It would be sufficient to store it once in the main record.

I'm talking about database normalisation. You don't seem to know the term, as you answer your tables are "normally dbf tables"... <s>.

Bye, Olaf.

 

[falconsight]

Also remember than "type" is a VFP reserved word. It could cause unexpected results used as a field name.
Especially if you try to copy or export the data to other files.