RE: Update a column in a table from a flat file

My definitiion of a flat file is just a plain text file. No indexes, not a PF. It is the output generated from Crystal Report Writer. How would I specifity
this non database file? The nonkeyed file will contain two columns: the row
identifier in FACILITY_PATIENT, and the value to change a selected column to.


UPDATE FACILITY_PATIENT SET FMRN = xxx
WHERE MEDICAL_RECORD_NUMBER = yyy

The yyy value is the first column in the nonkeyed file. The xxx value is the
value to apply to the named column of the selected row.

But, how is this non SQL file identified?

John McKee

Quoting vhamberg@xxxxxxxxxxx:

Alan

You know all this, I'm just piggy-backing on your post.

If "flat file" means a one-field record, substr(field, 5, 10) works - the contents starting at position 5 for 10 characters.

If a "non-keyed file", no different from any other physical file.

I really wish we'd stop talking about flat files anymore - that term is pretty meaningless, seems to me, in this day and age. A PF is a PF is a table is a table.

HTH
Vern

-------------- Original message --------------
From: Alan Shore <AlanShore@xxxxxxxx>

John,
Without sounding sarcastic, YOU tell the system how to update based upon
whatever match conditions apply.
When you said "flat" file, do you mean :-
a non-keyed file?
a file with one field in the record?


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill
midrange-l-bounces@xxxxxxxxxxxx wrote on 02/13/2008 10:46:30 AM:

> I am not at all proficient in SQL. I was not aware that SQL could update
a
> table, based on a flat file. How would SQL know where the values where
in the
> flat file?
>
> John McKee
>
> Quoting "John Arnold (MFS)" :
>
> > For the column where you want the new value use,
> >
> > Case when mrc = yyy then xxx end as id
> >
> > In your select statement that creates the flat file.
> >
> >
> > John Arnold
> > (301) 354-2939
> >
> >
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John McKee
> > Sent: Tuesday, February 12, 2008 11:34 PM
> > To: Midrange Systems Technical Discussion
> > Subject: Update a column in a table from a flat file
> >
> > My earlier question has been resolved. Can't skip a key field in a
> > database and get decent performance.
> >
> > Now, I wondering:
> >
> > Run the Crystal report. Export the rsssults to a flat file. First
> > field would be a complete primary key to the MS-SQL database. Third
> > field would be the new value for a single specified field.
> >
> > One possibility would be to modify the text file to read like this:
> >
> > update clinical set id= xxx where mrc=yyy
> >
> > The above line would be modified so that a text file would have several
> > thousand individual update commands. The file would be input to a
> > command line program, name eludes me for the moment.
> >
> > Is there a way to do all the updates with a single command?
> >
> > John McKee
> >
> > --