NEWBIE: replacing a field from one table to another 
Author Message
 NEWBIE: replacing a field from one table to another

Hello,

I am trying to write a program that will replace the contents of a field in
one table to a field in another table.  Both tables are indexed on the
userid field.  I can successfully replace the first record but all
subsequent records are ignored.  I tried adding a scan/endscan but it didn't
help.  Any suggestions?

set exclusive off
open data data1
use table1.dbf in 0
use table2.dbf in 0

replace all table1.lastname with table2.dept for table1.userid=table2.userid

thanks,

Dave



Sun, 27 Aug 2000 03:00:00 GMT  
 NEWBIE: replacing a field from one table to another

Quote:

>I am trying to write a program that will replace the contents of a field in
>one table to a field in another table.  Both tables are indexed on the
>userid field.  I can successfully replace the first record but all
>subsequent records are ignored.  I tried adding a scan/endscan but it didn't
>help.  Any suggestions?

>set exclusive off
>open data data1
>use table1.dbf in 0
>use table2.dbf in 0

>replace all table1.lastname with table2.dept for table1.userid=table2.userid

Try this:

        USE table1 IN 0 INDEX TAG userid
        USE table2 IN 0 INDEX TAG userid

        SELECT table1
        GO TOP
        DO WHILE ! EOF([table1])
                SELECT table2
                IF SEEK(table1.userid, [table2])
                        SELECT table1
                        REPLACE table1.lastname WITH table2.dept
                ENDIF SEEK(table1.userid, [table2])
                SKIP IN table1
        ENDDO WHILE ! EOF([table1])

I prefer DO WHILE in this situation because I have seen SCAN get
confused while doing something like this.

Hope this helps...

Christopher Reed


"The oxen are slow, but the earth is patient."



Mon, 28 Aug 2000 03:00:00 GMT  
 NEWBIE: replacing a field from one table to another

Quote:

> Hello,

> I am trying to write a program that will replace the contents of a field in
> one table to a field in another table.  Both tables are indexed on the
> userid field.  I can successfully replace the first record but all
> subsequent records are ignored.  I tried adding a scan/endscan but it didn't
> help.  Any suggestions?

> set exclusive off
> open data data1
> use table1.dbf in 0
> use table2.dbf in 0

> replace all table1.lastname with table2.dept for table1.userid=table2.userid

> thanks,

> Dave

Dave,

You could also try this:
OPEN DATA data1
USE Table1 IN 0
USE Table2 IN 0

SET ORDER TO userid IN Table1
SET ORDER TO userid IN Table2

SELECT Table1
SET RELATION TO userid INTO Table2
REPLACE ALL ;
  table1.lastname WITH table2.dept ;
  FOR !EOF('Table2')

Of course this wont do anything for table1 records that have no
corresponding userid in Table2, but it less code, and it should work.

HIH

Marc



Mon, 28 Aug 2000 03:00:00 GMT  
 NEWBIE: replacing a field from one table to another

Boy trie this !

set exclusive off
open data data1
use table1.dbf in 0
use table2.dbf in 0

SELECT table1
SCAN
    SELECT table2
    IF SEEK(table1.userid)
        SELECT table1
        REPLACE lastname WITH table2.dept
    ENDIF
ENDSCAN

This should work !
If you want to use a shorter command (like you did) you should first set up
a
relation between the tables. But I think the total code will be equal...
so...

Bye
Erik
Flatsoft

Quote:

>Hello,

>I am trying to write a program that will replace the contents of a field in
>one table to a field in another table.  Both tables are indexed on the
>userid field.  I can successfully replace the first record but all
>subsequent records are ignored.  I tried adding a scan/endscan but it
didn't
>help.  Any suggestions?

>set exclusive off
>open data data1
>use table1.dbf in 0
>use table2.dbf in 0

>replace all table1.lastname with table2.dept for

table1.userid=table2.userid

- Show quoted text -

Quote:

>thanks,

>Dave



Mon, 28 Aug 2000 03:00:00 GMT  
 NEWBIE: replacing a field from one table to another

Keep in mind that if the field you are replacing is the indexed field, as
soon as you change it, the record may move.  So...basically, don't do a
replace all on an indexed field.

--

Nancy Folsom
(abqnfatdamescom--replace the word at and put a dot before the com)

Quote:

>Hello,

>I am trying to write a program that will replace the contents of a field in
>one table to a field in another table.  Both tables are indexed on the
>userid field.  I can successfully replace the first record but all
>subsequent records are ignored.  I tried adding a scan/endscan but it
didn't
>help.  Any suggestions?

>set exclusive off
>open data data1
>use table1.dbf in 0
>use table2.dbf in 0

>replace all table1.lastname with table2.dept for

table1.userid=table2.userid

- Show quoted text -

Quote:

>thanks,

>Dave



Mon, 28 Aug 2000 03:00:00 GMT  
 NEWBIE: replacing a field from one table to another

Nahhhhh......... ditch all the dbase stuff  <grin>

UPDATE table1, table2 SET table1.userid = table2.userid
WHERE table2.keyfieldr=table1.keyfield

This assumes that each table is keyed on a different field than the userid
field that you
are updating in the first table.

Best wishes,

--- Larry

Quote:

>Hello,

>I am trying to write a program that will replace the contents of a field in
>one table to a field in another table.  Both tables are indexed on the
>userid field.  I can successfully replace the first record but all
>subsequent records are ignored.  I tried adding a scan/endscan but it
didn't
>help.  Any suggestions?

>set exclusive off
>open data data1
>use table1.dbf in 0
>use table2.dbf in 0

>replace all table1.lastname with table2.dept for

table1.userid=table2.userid

- Show quoted text -

Quote:

>thanks,

>Dave



Mon, 28 Aug 2000 03:00:00 GMT  
 NEWBIE: replacing a field from one table to another

David

You have to set a relation between the two tables to do this replace.  if
both tables are index on userid then setting the relation is a two step
process.

1. Set the order of the child table
2. select the parent table and set the relation.

Then the replace all will work.

Using the scan/end scan  would work also but you would have to find the
record in the child table for each loop (unless you set the relation like
above).

Quote:

>Hello,

>I am trying to write a program that will replace the contents of a field in
>one table to a field in another table.  Both tables are indexed on the
>userid field.  I can successfully replace the first record but all
>subsequent records are ignored.  I tried adding a scan/endscan but it
didn't
>help.  Any suggestions?

>set exclusive off
>open data data1
>use table1.dbf in 0
>use table2.dbf in 0

>replace all table1.lastname with table2.dept for

table1.userid=table2.userid

- Show quoted text -

Quote:

>thanks,

>Dave



Tue, 29 Aug 2000 03:00:00 GMT  
 NEWBIE: replacing a field from one table to another

Yeah, I decided I loved UPDATE in FoxPro too.. until I ran into one
interesting (but logical) gotcha.  I used UPDATE to update fields in a
parameterized remote view (back end SQL) in which I had buffering mode set
optimistic.  UPDATE, of course, moves the record pointer, even if you are
just updating one record.  That really screws with row buffering.  I
decided I wasn't setting any table buffering on so I just went back to
using good old REPLACE.

Although, I guess we truly aren't talking about the same scenario, at all.
But I found I had to bear that in mind in my given situation.



Quote:
> Nahhhhh......... ditch all the dbase stuff  <grin>

> UPDATE table1, table2 SET table1.userid = table2.userid
> WHERE table2.keyfieldr=table1.keyfield



Fri, 01 Sep 2000 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Newbie Q: Displaying Fields from Related Table

2. Question - Grid with Fields from more than one Table

3. transfer a field data from one table to another

4. copy GEN - fields from one table to another

5. transfer a field data from one table to another

6. replace table1.field with table2.field

7. Add one record to the table if one already exist warn

8. copy record from one table to another one

9. question: replacing one bmp with another

10. Insert values from one table into another table.

11. Why a view can not be created with one table linked to more than two tables

12. Wizstyle.vcx (add button) in relationship (one table to another table)

 

 
Powered by phpBB® Forum Software