Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating Multiple Records

Status
Not open for further replies.

970935726

Programmer
Joined
Dec 17, 2005
Messages
1
Location
VE
I am a novice at this and I am trying to update the contents of one table with those of another. This is a multiple record update.

I am using a query similar to this:

update Articles;
set Articles.Path = (select Oldart.Path from Oldart where Oldart.id = Articles.id);
where exists (select Oldart.Path from Oldart where Oldart.id = Articles.id)

The goal is to update the "path" field in the new Articles table with the corresponding record in the old Old art table.

I keep getting an error message with the statement above (missing ")" in function name).

I have tried all kinds of variations to the statement above to no avail. Is this statement not valid? Is there another way to achieve the same goal?

I am using Visual FoxPro 6.0.

Thanks,

Edgar
 
I think your problem may be that you're nesting too many levels of SQL. A SELECT statement returns a set of values (a cursor, table, or array) and you're trying to store this set into the single field Articles.Path. You know and I know that this set will only hold a single record but Fox doesn't know that.

An easier way to achieve the result you want would be to establish a relation between the Articles and OldArt table with SET RELATION TO. You can then just say:
[TT]
Replace Articles.Path With OldArt.Path
[/TT]
and let the relation take care of matching the correct records.

Geoff Franklin
 
If Articles.ID is unique, then the "set" returned by the subselect will be one record.

I learnt SQL before I learnt Fox and (at the time) there was a rigid distinction between a variable and a set of variables; you couldn't put a set into a variable - even if the set contained only a single variable. I assumed this restriction would apply to Fox too so I never even tried it.

I've just tried a simple example in VFP 8 and got an error. Tried it in VFP 9 and - to my great surprise - it ran. One learns something new everyday.

Geoff Franklin
 
I was wondering if you have multiple columns in a tabel that you want to update on condition of multiple other columns what you would do. This is how I do one update, but I have several different helicopters that need to be updated. If model column of helicopter is 269C then set total time to the 269C column.
Code:
UPDATE flightdata SET flightdata.269C = flightdata.[Total Time]
WHERE flightdata.[A/C Make&Model]="269C";
Is there a way to to this all in one query or do I just need to create 10 different querys?

Thanks
 
sorry I posted in the wrong forum. My question is about Access.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top