SQL Problem ?

[DaveL]

I am trying to update a column in FileA (field1) with a column from FileB (field1) where the key fields, field2 and field3, match.

The program is updating the field, but with blanks instead of the value from fileB.field1.

Any ideas on what I am doing wrong?

Thanks!

UPDATE fileA;
SET fileA.field1 = fileB.field1;
WHERE EXISTS (SELECT fileB.field1 FROM fileB WHERE (fileB.field2 = fileA.field2) AND (fileB.field3 = fileA.field3)) ;

 

[webpager]

Not much experience of UPDATE but could yoo be updating with the value of the field name ie. 0 rather than the value of the field`s contents

 

[ramani]

Obvious... I feel the construction is wrong...

UPDATE fileA;
SET fileA.field1 = fileB.field1;
WHERE EXISTS (SELECT fileB.field1 FROM fileB , fileA WHERE (fileB.field2 = fileA.field2) AND (fileB.field3 = fileA.field3)) ;

I hope this helps
ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[DaveL]

Thanks for the response!

Following is the updated code I tried. It still doesn't seem to be working. Any ideas? Is there simpler method in VFP that I should be using to get my intended results?

Again, when I run the program, it looks like it is updating each row. But, not with the value of "fileB.field1". If I replace "fileB.field1" with "XW", the program works and "XW" is loaded into each row?

??

UPDATE fileA ;
SET fileA.field1= fileB.field1;
WHERE EXISTS 	(SELECT fileA.field2, fileA.field3, fileB.field1 ;
				FROM fileA, fileB;
				WHERE (fileA.field2 = fileB.field2) AND (fileA.field3 = fileB.field3) ;
[\code]

 

[ramani]

I suggest you create an updatable view ..
SELECT FileA.field1, FileB.field1 FROM FileA, FileB Where FileA.Field2 = FileB.Fileld2 .AND. etc... conditions.

You can open this view, requery() and update FileA.Field1 with FIleB.Field1. This is a reliable solution and easy.

ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com