update with subqueries 1

[hammertounge]

Hi:

I am new to foxpro. I inserted a bunch of data into a table but I forgot to include one field. I am trying to use sql to insert the field, something like this:

update ttable_1 from table_2
set table_1.MissedField = table_2.MissedField
where table1.unique_ID = Table_2.unique_ID

I may have some syntax errors but the code above should give you the general idea. I can make it work in SQL Server 2000 but not FoxPro. Any ideas????

Thanks in advance.

Hammertounge

 

[Mike Gagnon]

hammertounge

update ttable_1 from table_2

I don't think you need "from table_2"

UPDATE myTable set myTable.Field1 = myTable2.field1 WHERE myTable1.uniqueId = myTable2.uniqueID Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ramani]

HI
Assuming you have set the order to UniqueId in Table_2 from where you are updating to Table_1


SELECT table_1
SET RELATION TO uniQue_id into Table2
REPLACE ALL MissedField WITH table_2.MissedField

This should do the trick.. instead of an SQL update.
ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com
Merry Christmas & Happy New Year

 

[hammertounge]

I have tried the following code and it seems to only sork for one record.

update table_1 set table_1.value1 = table_2.value2 where table_1.ID_1 = table_2.ID_2

update table_1 set table_1.value1 = table_2.value2 where table_1.ID_1 in (select ID_2 from table_2)

The queries above do not seem to work. Does anyone have any ideas It should be noted that these tables are fairly large, table_1 has over 1 million records and table_2 has about 50,000 records.

Thanks in advance.

Thanks in advance