Thread: Update problem.
Hello
I have a table created as the following
CREATE TABLE mod48_00_2007
(
id text,
n_gen serial NOT NULL,
formstore text,
te_cognome text,
te_paternita text,
te_nome text,
te_sesso text,
te_dtnasc text,
te_attnasc text,
te_luonasc text,
te_provstato text,
te_indi text,
te_prov text,
te_richiesta text,
atto_forma text,
rev_test text,
atto_dt text,
atto_num text,
dt_olog text,
cod_notaio text,
pa_cognome text,
pa_nome text,
pa_qual text,
pa_indirizzo text,
pa_civ text,
pa_cap text,
pa_pro text,
pa_sede text,
pa_estero text,
pa_data text,
f_olo text,
f_pub text,
f_rev text,
f_seg text,
f_spe text,
atto_riferimento text,
ratto_dt text,
ratto_num text,
rdt_olog text,
r_cognome text,
r_nome text,
r_qual text,
r_sede text,
r_estero text,
dt_oggi text,
n_pub_aut text,
dt_tr_rgt text,
sche_singola text,
sche_multipla text,
n_fog text,
tot_n_fog text,
reg_anno text,
username text,
date_inserted text,
time_inserted text,
deleted text,
date_deleted text,
time_deleted text,
f_tes text,
CONSTRAINT mod48_00_2007_pkey PRIMARY KEY (n_gen)
)
(
id text,
n_gen serial NOT NULL,
formstore text,
te_cognome text,
te_paternita text,
te_nome text,
te_sesso text,
te_dtnasc text,
te_attnasc text,
te_luonasc text,
te_provstato text,
te_indi text,
te_prov text,
te_richiesta text,
atto_forma text,
rev_test text,
atto_dt text,
atto_num text,
dt_olog text,
cod_notaio text,
pa_cognome text,
pa_nome text,
pa_qual text,
pa_indirizzo text,
pa_civ text,
pa_cap text,
pa_pro text,
pa_sede text,
pa_estero text,
pa_data text,
f_olo text,
f_pub text,
f_rev text,
f_seg text,
f_spe text,
atto_riferimento text,
ratto_dt text,
ratto_num text,
rdt_olog text,
r_cognome text,
r_nome text,
r_qual text,
r_sede text,
r_estero text,
dt_oggi text,
n_pub_aut text,
dt_tr_rgt text,
sche_singola text,
sche_multipla text,
n_fog text,
tot_n_fog text,
reg_anno text,
username text,
date_inserted text,
time_inserted text,
deleted text,
date_deleted text,
time_deleted text,
f_tes text,
CONSTRAINT mod48_00_2007_pkey PRIMARY KEY (n_gen)
)
At a surtain point i need to replace a record with another
For example i have inserted 4 records. (1, 2 , 3 , 4) I need to replace all the values from the record 4 to the record 2 but keeping the n_gen serial key. And then delete the record 4. So that the next record i insert will take the n_gen 4
How can i do this?
Shavonne Wijesinghe
On Mon, Apr 02, 2007 at 04:52:46PM +0200, Shavonne Marietta Wijesinghe wrote: > At a surtain point i need to replace a record with another > > For example i have inserted 4 records. (1, 2 , 3 , 4) I need to > replace all the values from the record 4 to the record 2 but > keeping the n_gen serial key. And then delete the record 4. So that > the next record i insert will take the n_gen 4 If I understand you correclty, you can do an UPDATE to record 4 to record 2, then do a setval() on the sequence. The setval() is tricky, though, because you have the problem that other connections could be using it. I'd lock the table in question while you did all this. A -- Andrew Sullivan | ajs@crankycanuck.ca Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris
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 so it will be like that? But i have to set each column name = to the column name. And i have to do it for echt field? isn't there any other way.. "I hate writting :P" And Andrew can explain a bit the setval() Thanks Shavonne Wijesinghe From: "Andrew Sullivan" <ajs@crankycanuck.ca> To: <pgsql-sql@postgresql.org> Sent: Monday, April 02, 2007 5:17 PM Subject: Re: [SQL] Update problem. > On Mon, Apr 02, 2007 at 04:52:46PM +0200, Shavonne Marietta Wijesinghe > wrote: >> At a surtain point i need to replace a record with another >> >> For example i have inserted 4 records. (1, 2 , 3 , 4) I need to >> replace all the values from the record 4 to the record 2 but >> keeping the n_gen serial key. And then delete the record 4. So that >> the next record i insert will take the n_gen 4 > > If I understand you correclty, you can do an UPDATE to record 4 to > record 2, then do a setval() on the sequence. The setval() is > tricky, though, because you have the problem that other connections > could be using it. I'd lock the table in question while you did all > this. > > A > > -- > Andrew Sullivan | ajs@crankycanuck.ca > Unfortunately reformatting the Internet is a little more painful > than reformatting your hard drive when it gets out of whack. > --Scott Morris > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
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