Updating one table from another

[bebbo]

If I have two tables with similar fields is there a way I can update one table from another if records do not exist.

EG

Table1 has 3 fields plu, descr and quantity

This table is the main table and is used frequently.

I have a similar table (table2) which is only opened from time to time. Table2 is a copy of table1. If table1 has had extra data added since Table1 was last opened is there an easy way I can add the extra data to table2.

Can I check the contents of one table against another and update the table should it need updating?? Hope I make myself clear

 

[Neil Toulouse]

Will the existing records in Table1 be changed when compared to Table2, or are you just checking for any new records?

What is the unique field in these tables?

Neil "I like work. It fascinates me. I can sit and look at it for hours..."

 

[bebbo]

Hi,

The PLU is the unique field, Desrc may change and also records may be added to table1. Therefore if extra records are in table1 these will be required in table2 (when it is next opened)

 

[Neil Toulouse]

SELECT Table1
SCAN

SELECT Table2
LOCATE FOR Plu == Table1.Plu && if you have an index use SEEK() for speed

IF FOUND() && Update Table2's record

REPLACE descr WITH Table1.Descr, Quantity WITH Table1.Quantity IN Table2

ELSE && Add the new record

SELECT Table1
SCATTER TO MEMVAR MEMO

SELECT Table2
APPEND BLANK
GATHER FROM MEMVAR MEMO

ENDIF

SELECT Table1
ENDSCAN

HTH
Neil "I like work. It fascinates me. I can sit and look at it for hours..."

 

[EdLeafe]

Here's a simple routine that will handle this:

SELECT Table1
SCAN
lcPLU = Table1.PLU
SCATTER MEMO NAME loTable1

SELECT Table2
* Assume a tag on PLU
IF SEEK(lcPLU, "PLU&quot
SCATTER MEMO NAME loTable2

* Works great in VFP 8. In VFP 7, NULL values will
* cause the comparison to fail.
IF loTable1 # loTable2
* Records are different
GATHER MEMO NAME loTable1
ENDIF
ELSE
* No such record; insert
APPEND BLANK
GATHER MEMO NAME loTable1
* In VFP 8 you could do it in one line:
* INSERT INTO Table2 FROM NAME loTable1
ENDIF
ENDSCAN

-- Ed
-- Get great VFP support in a new forum filled with Microsoft MVPs!
--

http://opentech.leafe.com