pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate in all tables
7+ messages / 6 participants
[nested] [flat]
* Update in all tables
@ 2006-02-22 17:59 Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
0 siblings, 1 reply; 7+ messages in thread
From: Judith Altamirano Figueroa @ 2006-02-22 17:59 UTC (permalink / raw)
To: pgsql-sql
Hello everybody I need to update a field with the same value in the
tables of my data base but this field exists in almost all tables and
has the same value, I don't want to code a script, so my question is if
there is some way to update that field with a query and affects all the
tables that contain the field?
Thanks in advanced, regards!!!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Update in all tables
@ 2006-02-22 18:08 Judith <jaltamirano@correolux.com.mx>
0 siblings, 2 replies; 7+ messages in thread
From: Judith @ 2006-02-22 18:08 UTC (permalink / raw)
To: pgsql-sql
Hello everybody I need to update a field with the same value in the
tables of my data base but this field exists in almost all tables and
has the same value, I don't want to code a script, so my question is if
there is some way to update that field with a query and affects all the
tables that contain the field?
Thanks in advanced, regards!!!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update in all tables
@ 2006-02-22 18:33 Janning Vygen <vygen@gmx.de>
parent: Judith <jaltamirano@correolux.com.mx>
1 sibling, 0 replies; 7+ messages in thread
From: Janning Vygen @ 2006-02-22 18:33 UTC (permalink / raw)
To: pgsql-sql
Am Mittwoch, 22. Februar 2006 19:08 schrieb Judith:
> Hello everybody I need to update a field with the same value in the
> tables of my data base but this field exists in almost all tables and
> has the same value, I don't want to code a script, so my question is if
> there is some way to update that field with a query and affects all the
> tables that contain the field?
no. but you can do with a trigger ON UPDATE
but what is so evil about a script like this:
#!/bin/sh
TABLES="A B C D"
for TAB in $TABLES
do
psql -c "UPDATE $TAB set field = 'new' where field = 'old'" mydbname;
done
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update in all tables
@ 2006-02-22 19:12 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Judith <jaltamirano@correolux.com.mx>
1 sibling, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2006-02-22 19:12 UTC (permalink / raw)
To: Judith <jaltamirano@correolux.com.mx>; +Cc: pgsql-sql
On Wed, 2006-02-22 at 12:08, Judith wrote:
> Hello everybody I need to update a field with the same value in the
> tables of my data base but this field exists in almost all tables and
> has the same value, I don't want to code a script, so my question is if
> there is some way to update that field with a query and affects all the
> tables that contain the field?
Are those fields all dependent FK fields? If so, then declaring those
foreign keys as on update cascade is all you need.
If they're not related that way, then you'll have to script it.
If you need them all to change at the same time (or all roll back in the
event of an update failure) then you can wrap the changes in a
transaction (begin/end pair) and it'll be atomic.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update in all tables
@ 2006-02-22 21:13 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Sullivan @ 2006-02-22 21:13 UTC (permalink / raw)
To: pgsql-sql
On Wed, Feb 22, 2006 at 11:59:06AM -0600, Judith Altamirano Figueroa wrote:
> Hello everybody I need to update a field with the same value in the
> tables of my data base but this field exists in almost all tables and
> has the same value, I don't want to code a script, so my question is if
> there is some way to update that field with a query and affects all the
> tables that contain the field?
No, sorry. While we're at it, though, if you have the same field in
several tables, it's a good sign that your database is badly
normalised. You shouldn't have to update more than one table (which
is why there isn't a way to do this automatically).
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 in all tables
@ 2006-02-22 21:16 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2006-02-22 21:16 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
On Wed, 2006-02-22 at 15:13, Andrew Sullivan wrote:
> On Wed, Feb 22, 2006 at 11:59:06AM -0600, Judith Altamirano Figueroa wrote:
> > Hello everybody I need to update a field with the same value in the
> > tables of my data base but this field exists in almost all tables and
> > has the same value, I don't want to code a script, so my question is if
> > there is some way to update that field with a query and affects all the
> > tables that contain the field?
>
> No, sorry. While we're at it, though, if you have the same field in
> several tables, it's a good sign that your database is badly
> normalised. You shouldn't have to update more than one table (which
> is why there isn't a way to do this automatically).
Unless they're all FK/PK pairs, in which case you on update / delete
cascade FKs.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update in all tables
@ 2006-02-22 21:25 Owen Jacobson <ojacobson@osl.com>
0 siblings, 0 replies; 7+ messages in thread
From: Owen Jacobson @ 2006-02-22 21:25 UTC (permalink / raw)
To: pgsql-sql
Scott Marlowe wrote:
> On Wed, 2006-02-22 at 15:13, Andrew Sullivan wrote:
>
> > No, sorry. While we're at it, though, if you have the same field in
> > several tables, it's a good sign that your database is badly
> > normalised. You shouldn't have to update more than one table (which
> > is why there isn't a way to do this automatically).
>
> Unless they're all FK/PK pairs, in which case you on update / delete
> cascade FKs.
It's entirely too bad this has to be designed into the DDL for the table. It'd be nice if you could specify "CASCADE" on UPDATE or DELETE queries. I don't see that it's any more of a gun than ON UPDATE/ON DELETE CASCADE in the foreign key definition.
-Owen
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-02-22 21:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-22 17:59 Update in all tables Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
2006-02-22 21:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-02-22 21:16 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-22 18:08 Update in all tables Judith <jaltamirano@correolux.com.mx>
2006-02-22 18:33 ` Janning Vygen <vygen@gmx.de>
2006-02-22 19:12 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-22 21:25 Re: Update in all tables Owen Jacobson <ojacobson@osl.com>
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