[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