pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Update Problem
7+ messages / 3 participants
[nested] [flat]
* Re: Update Problem
@ 2006-06-06 11:10 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Christian Paul Cosinas <cpc@cybees.com>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2006-06-06 11:10 UTC (permalink / raw)
To: pgsql-sql
On Tue, Jun 06, 2006 at 01:04:26PM -0000, Christian Paul Cosinas wrote:
>
>
> Sometimes only column1 and column2 is updated and column3 is not updated.
Column 3 was already set to 08:00 AM?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
^ permalink raw reply [nested|flat] 7+ messages in thread
* Update Problem
@ 2006-06-06 13:04 Christian Paul Cosinas <cpc@cybees.com>
0 siblings, 1 reply; 7+ messages in thread
From: Christian Paul Cosinas @ 2006-06-06 13:04 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm encountering some weird problem on Update.
I have a program that updates a table and it updates more than 1 column at a
single query.
Like this one:
Update table1 set column1 = 'a', column2 = 'b', column3 = time '08:00 AM'
where column1 = 'c'
Sometimes only column1 and column2 is updated and column3 is not updated.
What could be the possible cause of this?
Thanks,
IANIAN
^ permalink raw reply [nested|flat] 7+ messages in thread
* Update problem.
@ 2007-04-02 14:06 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 0 replies; 7+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-04-02 14:06 UTC (permalink / raw)
To: pgsql-sql
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)
)
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
www.studioform.it
Le informazioni contenute nella presente comunicazione e i relativi allegati possono essere riservate e sono, comunque destinate esclusivamente alle persone o alla Società sopra indicati.
La diffusione, distribuzione e/o copiature del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dell'art. 616 c.p., che ai sensi del D. Lgs. n. 196/2003.
Se avete ricevuto questo messaggio per errore, Vi preghiamo di distruggerlo e di informarci immediatamente per telefono allo 0039362595044 o inviando un messaggio all'indirizzo e-mail
amministrazione@studioform.it
The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only.
Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the sender.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Update problem.
@ 2007-04-02 14:52 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 7+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-04-02 14:52 UTC (permalink / raw)
To: pgsql-sql
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)
)
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update problem.
@ 2007-04-02 15:17 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Sullivan @ 2007-04-02 15:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update problem.
@ 2007-04-03 07:13 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 7+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-04-03 07:13 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update problem.
@ 2007-04-03 16:16 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2007-04-03 16:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-04-03 16:16 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-06 13:04 Update Problem Christian Paul Cosinas <cpc@cybees.com>
2006-06-06 11:10 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-04-02 14:06 Update problem. Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-04-02 14:52 Update problem. Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-04-02 15:17 ` Re: Update problem. Andrew Sullivan <ajs@crankycanuck.ca>
2007-04-03 07:13 ` Re: Update problem. Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-04-03 16:16 ` Re: Update problem. Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox