Updating Multiple Records

[970935726]

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

 

[alvechurchdata]

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

http://www.alvechurchdata.co.uk

 

[alvechurchdata]

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

http://www.alvechurchdata.co.uk

 

[southernweyr]

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.

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

 

[southernweyr]

sorry I posted in the wrong forum. My question is about Access.