Date: Tue, 3 Apr 2007 12:16:06 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Update problem.
Message-ID: <20070403161606.GA1145@phlogiston.dyndns.org>
References: <00c301c77536$938a32e0$1102a8c0@dream>
	<20070402151726.GA30052@phlogiston.dyndns.org>
	<002701c775bf$847c9930$1102a8c0@dream>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <002701c775bf$847c9930$1102a8c0@dream>
User-Agent: Mutt/1.5.9i

On Tue, Apr 03, 2007 at 09:13:00AM +0200, Shavonne Marietta Wijesinghe wrote:
> Thanks. But to do the UPDATE i have to write each column name (for recrd 4) 
> and with its column name (for record 2) which is quite alot to write :P
> 
> UPDATE MOD48_00_2007 SET te_cognome= te_cognome, te_paternita= te_paternita 
> WHERE N_GEN= 9

Shouldn't need to.  UPDATE [table] SET somecolumn = newval WHERE
othercolumn = criterionval just changes the value of "somecolumn" and
leaves everything else alone.  You of course have to name the columns
you're trying to change or use as selection criteria.

> And Andrew can explain a bit the setval()

The setval() function sets the current value of a sequence.  The
problem that you have is that there's no way to LOCK a sequence, so
you might find that you can't do it effectively.  LOCKing the calling
table, if it's the only thing that calls this sequence, might help. 
But you could easy run into a race condition where someone inserts
and gets the nextval() of 5, then you update your rows valued 4 to 2
and set the value via setval() to 4.  Next, 4 gets inserted, but the
_next_ operation that comes along will try to insert 5, and get an
error.  (Sorry if this isn't clear.  You should read the manual
carefully about what sequences are and are not intended to do.  If
I've understood your intention correctly, you're trying to get a
sequence to give you a gapless range in the table.  Sequences are
designed with that requirement explicitly excluded, and you might be
better to try another method.)

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Everything that happens in the world happens at some place.
		--Jane Jacobs