Update a table

[jimstarr]

Looking for a quick way update one table from another. The tables have identical structures, and have lots of fields. I want to replace each field in table A with the corresponding field from table B so long as the field from table B isn't EMPTY(). I expect that there are a lot of slick ways to do this, but can't think of any today. Thanks!

Jim

 

[jimstarr]

Hi Mike,

The common identifier is kind of irrelevant in this particular case because this is a 1-record-at-a-time kind of update. Get a record from table B, locate the corresponding record in table A, ask the user if the update should be done, (if yes) do the update.

I like your approach with field names. I was also thinking that SCATTER/GATHER would be perfect if there were a statement like:

SCATTER MEMVAR FOR NOT EMPTY()

Then the whole deal would be:

SCATTER MEMVAR FOR NOT EMPTY()
GATHER MEMVAR

Thanks!!

Jim

 

[DSummZZZ]

I prefer SCATTER/GATHER myself, but there aren't any sort of scope clauses like you're implying, only a record by record thing.
Anyway, if you have an index on table B, you can relate it like Mike demo'd. Other wise, you'll have to do something like a LOCATE.
Here's an example with SCATTER though:

SELECT 0
USE TableB EXCLUSIVE ORDER SomeTag 
SELECT TableA
SET RELATION TO SomeField INTO TableB

SCAN
   IF !EMPTY(TableB.SomeField)
      SELECT TableB
      SCATTER MEMVAR
      INSERT INTO TableA FROM MEMVAR
   ENDIF
   SELECT TableA  &... not really necessary, but warm fuzzy
ENDSCAN

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[jimstarr]

Hi Dave,

Thanks for your reply. I believe INSERT will create a new record, which is not what I want. Also SCATTER MEMVAR will scatter all the fields, not just the non-empty ones.

Jim

 

[DSummZZZ]

Ugh. You're right.
I guess - once again - my fingers got ahead of my brain. Sorry.

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Dan Freeman]

You could use scatter, but you need to build an EXCEPT clause for fields that are empty.

lcClause = ""
If Empty(field1)
lcClause = lcClause + "field1,"
Endif
.... etc.

Use Afields() to build the list generically, and then you can

SCATTER MEMVAR EXCEPT lcClause

The only thing you'll need to manage is releasing the memvars so that you don't get carry-overs from row to row, but again, use Afields() to build a list to release.

 

[jimstarr]

Thanks Mike and Dave.

The solution I settled on is as follows:

SELECT myupdates
SCATTER NAME objx
=AMEMBERS(xa, objx)

FOR i = 1 TO ALEN(xa) && remove empty fields
namex = xa(i)
IF EMPTY(objx.&namex)
REMOVEPROPERTY(objx, namex)
ENDIF
ENDFOR

SELECT mytable
GATHER NAME objx

Jim

 

[jimstarr]

Hi Mike,

An interesting comparison, which I'll file away for future reference. Speed happens to be not an issue in this case. Thanks!

Jim