Information générale
Catégorie:
Codage, syntaxe et commandes
Titre:
Update table with data from other table
I have following FOXPRO (FoxPRO 5.0 service Pack 2) problem :
I have two tables, containing different information. These tables may be
connected by use of one column containing an integervalue.
My problem is how I can 'copy' one column of this table towards the
other table.
EX.
TableOne TableTwo
fieldA1 fieldB fieldA2 fieldC
100 45 100 0
101 21 101 0
102 44 102 0
103 88 103 0
104 70 104 0
105 21 105 0
should become :
TableOne TableTwo
fieldA1 fieldB fieldA2 fieldC
100 45 100 45
101 21 101 21
102 44 102 44
103 88 103 88
104 70 104 70
105 21 105 21
Currently I do the following :
&& create temporary index on table two
Select TableTwo
index on field2A TAG theIndex
SET ORDER TO TheIndex
Select tableOne
SCAN
select TableTwo
Seek TableOne.fieldA1
IF FOUND()
REPLACE TableOne.fieldA1 WITH TableTwo.fieldA2
IN TableOne
ENDIF
Select TableOne
ENDSCAN
Of course, this works but far too slow at my opinion.
Is there someone who knows how to deal with this problem in a more
efficient way , for example by use of an SQL UPDATE like :
UPDATE TableOne SET fieldB = (SELECT tableTwo.fieldC FROM
tabelTwo WHERE TableTwo.fieldA2 = TableOne.fieldA1)
JDG
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement