[Babase] updating a range of records
Karl O. Pinc
kop at meme.com
Mon Nov 19 12:40:02 EST 2007
On 11/19/2007 10:56:34 AM, kfenn wrote:
> Hi Karl,
>
> Laurence will be periodically updating the dates in a small range of
> fecal table records. I think the way for her to do this is to use
> the UPDATE command and use the sid to specify the records she wants
> to alter.
>
> In FoxPro, she was able to use a "Replace with" dialog box that asked
> her to specify a starting point and give a numeric value for the
> number of subsequent records she wanted to fill in with her new
> value. Is there any equivalent in SQL that would let her say do the
> same thing. That is, to say "Start with this record, in this column
> and fill in such-and-such for the next 7 records"....or is UPDATE the
> way to go?
UPDATE is definitely the way to go.
Postgres has no concept of "the next 7 records", because there is no
ordering of the rows in the database. You can use UPDATE to update
rows based on any kind of selection parameter. If you want to update
all the ones with a range of Sids you can do that, or if you want to
change all of a particular date you can select on date, etc.
UPDATE fecal.admale_copy2
SET date = '2007-11-19'
WHERE sid >= 740 AND sid <= 747;
If you want to regularly update a completely arbitrary collection
of rows probably the best way to do it is to make another table
that contains the ids of the rows you want to update. (Or if you've
5 different collections of arbitrary rows rather than make a single
table make one table with 2 columns, the first column being the Sid
and the second column the name of the "collection". There's no
particular reason why the Sid needs to be unique, an Sid can
appear in more than one "collection" to be updated. Actually,
to be sane you'd want a third column, an Id for the "collections"
table so you can be sure you're really working with the row there
that you think you are.)
The UPDATE statement is more powerful than foxpro's REPLACE, because
you can join other tables. In this case I'm joining the table
that has the list of Sids to update (fecal.updatesids).
UPDATE fecal.admale_copy2
SET date = '2007-11-19'
FROM fecal.updatesids
WHERE fecal.admale_copy2.sid = fecal.updatesids.sid
AND fecal.updatesids.collection = 'Tuesday update';
Is this good or do we need a phone call?
Karl <kop at meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
More information about the Babase
mailing list