[Babase] updating a range of records

kfenn kfenn at princeton.edu
Mon Nov 19 13:11:10 EST 2007


Karl O. Pinc wrote:
>
> 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?
>

Thanks, Karl.  It makes good sense to me, but will let you know if we 
get stuck. 

Tabby


>
> Karl <kop at meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                  -- Robert A. Heinlein
>
> _______________________________________________
> Babase mailing list
> Babase at www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase

-- 
Tabby Fenn
Research Assistant

Dept of Ecology and Evolutionary Biology
401 Guyot Hall
Princeton University
Princeton, NJ  08544

609 258-6898 (Ph)
609 258-2712 (Fx)



More information about the Babase mailing list