Two tables updating records

[cacho1973]

Hello everyone! First post here, long time reader.

I have two FoxPro tables

e.g. Table1.dbf and Table2.dbf

Table1 with fields distance(numeric), time(character), type(char), address (char) Records=200
Table2 with fields distance(numeric), time(character) Records=25

If the distance in table2 matches the distance in table1, I need to update table1 time with table2 time.

Table1
Distance Time Type Address
408.31 123 1 54
408.99 1 65
410.55 1 55
412.22 444 1 66
415.55 1 78

Table2
Distance Time
408.99 555
410.55 632
415.55 688

Table1 after modification
Distance Time Type Address
408.31 123 1 54
408.99 555 1 65
410.55 632 1 55
412.22 444 1 66
415.55 688 1 78

I came up with a join tables command but only to a new table creating new columns, etc.

SELECT table1.distance, table1.time, table1.Type, table1.Address, table2.distance, table2.time
FROM TABLE1.DBF ;
LEFT OUTER JOIN TABLE2.DBF ;
ON TABLE1.distance = TABLE2.distance INTO TABLE TABLE1_MOD

*** the previous commamd will merge both tables into TABLE1_MOD, generating some records will null values so I filter not to include nulls
SET FILTER TO time <> ""
*** the following command will replace the time
replace ALL time_a WITH time_b
/////////////

I do no like the method I used, I do not know how to do it on the same table1 instead of creating a new one.

Thank you all!

Cacho

 

[foxmuldr3]

If I understand, you want table1's time to be table2's time? If so:

select table2
*Choose your poison:
*index on distance tag main
*index on distance to table2.idx

select table1
*Choose your poison:
*index on distance tag main
*index on distance to table1.idx

set relation to distance into table2
replace all time with table2.time for not eof('table2')

Untested, but should work.

Best regards,
Rick C. Hodgin

 

[cacho1973]

Rick, yes, I want table1's time to be table2's time for all records that match the table2 distance and time is not null, because time in table1 does not allow null values.

The code I wrote using yours as base, did not work, and it shows "Cannot update the cursor QUERY since it is read only"

CLOSE TABLES

SET PATH TO c:\xxxxx

select distance, time FROM Table2
*Choose your poison:
index on distance tag main
index on distance to table2.idx

select * FROM Table1
*Choose your poison:
index on distance tag main
index on distance to table1.idx

set relation to distance into rs_client
replace all time with table2.time for not eof('table2')

Thank you!

 

[foxmuldr3]

You don't need to do the select * from Table1 or Table2. The replace all command will work directly on the raw tables.

select 0
use table2
index on distance to table2.idx

select 0
use table1
*The following line is not really necessary, but for browsing you can use it
index on distance to table1.idx

set relation to distance into table2
replace all time with table2.time for not eof('table2') and not empty(table.time)

Best regards,
Rick C. Hodgin

 

[cacho1973]

CORRECTION TO LINE

It is not working yet...

CLOSE TABLES

SET PATH TO c:\xxxxx

select distance, time FROM Table2
*Choose your poison:
index on distance tag main
index on distance to table2.idx

select * FROM Table1
*Choose your poison:
index on distance tag main
index on distance to table1.idx

* CORRECTION
set relation to distance into table2
* END CORRECTION

replace all time with table2.time for not eof('table2')

Thank you!

 

[foxmuldr3]

Should be table2.time:

replace all time with table2.time for not eof('table2') and not empty(table2.time)

Best regards,
Rick C. Hodgin

 

[Olaf Doschke]

Welcome, cacho.

The sql way is to update-sql table1 from table2, which is also possible:

Update Table1 
   Set Time = Table2.Time
   From Table2 
   Where Table2.Distance = Table1.Distance

I tested with a bit of your sample data, shrinked to an even smaller set:

Create Cursor Table1 (Distance B, Time C(3))
Create Cursor Table2 (Distance B, Time C(3))

Insert Into Table1 Values (408.99, "1")
Insert Into Table1 Values (410.55, "1")
Insert Into Table1 Values (999.99, "0")

Insert Into Table2 Values (408.99, "555")
Insert Into Table2 Values (410.55, "632")

Update Table1 ;
   Set Time = Table2.Time ;
   From Table2 ;
   Where Table2.Distance = Table1.Distance

The record for Distance 999.99 is untouched, as it has no ne value in Table2.

You might want to change Where Abs(Table2.Distance-Table1.Distance)<0.01 to give it some tolerance for matching with almost matching distances. That depends on your needs, of course.

Like Ricks solution it helps to index on distances, in case you do the exact match. With the inexact Asb(difference) match an index won't accelerate this, but with your reccounts that wouldn't be a problem, it will only take split seconds to update 200 records from 25 others anyway.

Bye, Olaf.

 

[cacho1973]

Thank you guys! Ricks solution did work, although I will try Olaf's as well.

 

[JRB-Bldr]

I have two FoxPro tables
BUT
"Cannot update the cursor QUERY since it is read only"

That says that you are NOT working with a TABLE for the 'table' that you are attempting to update. Instead you are working with a ReadOnly Cursor (as the message indicates).

You might want to change how your application works so that it is using 'real' TABLES and/or Update-able Cursors (at the end of a query INTO CURSOR Results READWRITE).

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Just to get one thing straight: You didn't follow Ricks instructions. He said SELECT Table1 and SELECT Table2, not SELECT * FROM Table1 and SELECT distance, time FROM Table2.

Foxpro knows two different SELECT commands: SELECT from the SQL language and SELECT just for selecting a workarea. As a long time reader and foxpro developer, I assume you know workareas, if not, then please ask.

The prerequisite for SELECT Table1 and SELECT Table2 to work is, that Table1 and Table2 are opened (eg by USE or by a dataenvironment or any previous code). If that's not the case, simply USE Table1 IN 0 and USE Table2 IN 0 before using Rick's code. He was not merely hinting at writing SQL Queries.

Why your SQL SELECTs also work is, they 1) open the tables you query and 2) generate a cursor named QUERY, and the first step is what you need, the second step is just a waste of time in that case, as you don't want o generate cursors, but want to update the table1 itself. Take a look at the Datasession via the datasession window, after you did your version of Ricks code. You'd see Table1, Table2 and Query and perhaps Query2.

Bye, Olaf.

 

[cacho1973]

Olaf, thank you so much for the clarifications. I said I was a long time reader, but not a foxpro developer. This webpage has so many resources for many languages... Actually I am Visual Basic developer with experience in Access databases only, learning some Objective C, but now I have a new job, nothing to do with programming, but the databases here are in Foxpro... so someone asked if anyone knows how to "merge" two tables and I tried to learn how to do it. I did Clipper back in 90's but I do not recall too much... Anyway, the code went like this, and it worked like a charm!

CLOSE TABLES
USE Table2 IN 0
index on distance to Table2.idx
USE Table1 IN 0
set relation to distance into Table2
replace all time with Table2.time for not eof('Table2') AND NOT EMPTY(Table2.time)
CLOSE ALL

Thank you all!

 

[Olaf Doschke]

OK, fine.

And the SQL I gave didn't worked? It does only VFP9. If it's not working, then most probably because you use VFP8 or earlier.

The code you now use generate the INDEX ON DISTANCE every time, this is a thing you could also jsut do once and for all, if using the other syntqax creating a CDX index tag. CDX indexes are as permanent parts of a table as their fields.

The idx on the other side will only be updated if you open them with the table, you can stay with that, but then it would be good to erase it after the replace.

Bye, Olaf.