Re: looping through query to update column

On 2006-10-13, "Albe Laurenz" <all@adv.magwien.gv.at> wrote:
> You lock the table (with LOCK) or the row you're working on
> (with SELECT FOR UPDATE) so that nobody else can change it while
> you are working on it.
>
> You need something like ctid if your table has the fundamental flaw
> of lacking a primary key.

Looping over rows unnecessarily is a mistake.

You can add a SERIAL column to a table using ALTER TABLE, which will
automatically number the existing rows; this is a better way to fix a
lack of a primary key than messing around with ctids.

For a one-off update, use a temporary sequence:

create temporary sequence foo;
update table set recordid = nextval('foo');

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services