From jaltamirano@correolux.com.mx Wed Feb  22 20:57:06 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id F16C79DC868
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 16:57:06 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 79712-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 16:57:07 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from CORREO.correolux.com.mx
	(customer-200-79-53-218.uninet-ide.com.mx [200.79.53.218])
	by postgresql.org (Postfix) with ESMTP id 389829DC83A
	for <pgsql-sql@postgresql.org>; Wed, 22 Feb 2006 16:57:03 -0400 (AST)
Received: from 192.168.7.58 ([192.168.7.58]) by correolux.com.mx with
	MailEnable ESMTP; Wed, 22 Feb 2006 11:56:59 -0600
Subject: Update in all tables
From: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary="=-8cbtgGHEqVcAQ/p2gyd0"
Date: Wed, 22 Feb 2006 11:59:06 -0600
Message-Id: <1140631146.5580.5.camel@Desarrollo>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-3) 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.119, HTML_MESSAGE=0.001]
X-Spam-Score: 0.12
X-Spam-Level: 
X-Archive-Number: 200602/272
X-Sequence-Number: 24282


--=-8cbtgGHEqVcAQ/p2gyd0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

    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!!!

--=-8cbtgGHEqVcAQ/p2gyd0
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.3.2">
</HEAD>
<BODY>
&nbsp;&nbsp;&nbsp; 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?<BR>
<BR>
&nbsp;&nbsp;&nbsp; Thanks in advanced, regards!!!
</BODY>
</HTML>

--=-8cbtgGHEqVcAQ/p2gyd0--





From jaltamirano@correolux.com.mx Wed Feb  22 18:07:02 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 858CA9DC838
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 14:07:02 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 53228-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 14:07:01 -0400 (AST)
X-Greylist: delayed 00:09:48.763603 by SQLgrey-
Received: from CORREO.correolux.com.mx
	(customer-200-79-53-218.uninet-ide.com.mx [200.79.53.218])
	by postgresql.org (Postfix) with ESMTP id D10CD9DC99D
	for <pgsql-sql@postgresql.org>; Wed, 22 Feb 2006 14:06:50 -0400 (AST)
Received: from [192.168.7.58] ([192.168.7.58]) by correolux.com.mx with
	MailEnable ESMTP; Wed, 22 Feb 2006 12:06:41 -0600
Message-ID: <43FCA8B0.3050803@correolux.com.mx>
Date: Wed, 22 Feb 2006 12:08:48 -0600
From: Judith <jaltamirano@correolux.com.mx>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
X-Accept-Language: es-es, es
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Update in all tables
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200602/268
X-Sequence-Number: 24278

   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!!!




From vygen@gmx.de Wed Feb  22 18:33:08 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 291A59DCC4C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 14:33:08 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 56634-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 14:33:07 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from pw6tiger.de (pw6tiger.de [217.160.140.116])
	by postgresql.org (Postfix) with SMTP id 71DBC9DCC3A
	for <pgsql-sql@postgresql.org>; Wed, 22 Feb 2006 14:33:04 -0400 (AST)
Received: (qmail 21654 invoked by uid 505); 22 Feb 2006 18:33:02 -0000
Received: from vygen@gmx.de by planwerk6 by uid 89 with qmail-scanner-1.14
	(virus scan:   Clear:.
	Processed in 0.264526 secs); 22 Feb 2006 18:33:02 -0000
Received: from pd95b4e37.dip0.t-ipconnect.de (HELO bos.airliquide.de)
	(vygen@planwerk6.de@217.91.78.55)
	by pw6tiger.de with SMTP; 22 Feb 2006 18:33:02 -0000
From: Janning Vygen <vygen@gmx.de>
To: pgsql-sql@postgresql.org
Subject: Re: Update in all tables
Date: Wed, 22 Feb 2006 19:33:36 +0100
User-Agent: KMail/1.8.3
References: <43FCA8B0.3050803@correolux.com.mx>
In-Reply-To: <43FCA8B0.3050803@correolux.com.mx>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200602221933.36387.vygen@gmx.de>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.148 required=5 tests=[AWL=0.148]
X-Spam-Score: 0.148
X-Spam-Level: 
X-Archive-Number: 200602/269
X-Sequence-Number: 24279

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




From smarlowe@g2switchworks.com Wed Feb  22 19:12:13 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id A99F19DC87A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 15:12:13 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 63825-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 15:12:13 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from exchange.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25]) by postgresql.org (Postfix) with ESMTP id E195A9DC877
	for <pgsql-sql@postgresql.org>; Wed, 22 Feb 2006 15:12:10 -0400 (AST)
Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com
	([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ;
	Wed, 22 Feb 2006 19:12:10 +0000
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	22 Feb 2006 13:12:10 -0600
Subject: Re: Update in all tables
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Judith <jaltamirano@correolux.com.mx>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <43FCA8B0.3050803@correolux.com.mx>
References: <43FCA8B0.3050803@correolux.com.mx>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1140635530.5777.32.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Wed, 22 Feb 2006 13:12:10 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.157 required=5 tests=[AWL=0.156,
	UNPARSEABLE_RELAY=0.001]
X-Spam-Score: 0.157
X-Spam-Level: 
X-Archive-Number: 200602/271
X-Sequence-Number: 24281

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.



From ajs@crankycanuck.ca Wed Feb  22 21:13:49 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id E6F719DC868
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 17:13:49 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 79861-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 17:13:51 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from r1.mycybernet.net (r1.mycybernet.net [209.5.206.8])
	by postgresql.org (Postfix) with ESMTP id C59549DC84B
	for <pgsql-sql@postgresql.org>; Wed, 22 Feb 2006 17:13:47 -0400 (AST)
Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]:34938
	helo=phlogiston.dydns.org)
	by r1.mycybernet.net with esmtp (Exim 4.50 (FreeBSD))
	id 1FC1Iy-0001in-WD
	for pgsql-sql@postgresql.org; Wed, 22 Feb 2006 16:13:49 -0500
Received: by phlogiston.dydns.org (Postfix, from userid 1000)
	id 2245A3FAF; Wed, 22 Feb 2006 16:13:48 -0500 (EST)
Date: Wed, 22 Feb 2006 16:13:48 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Update in all tables
Message-ID: <20060222211348.GE8741@phlogiston.dyndns.org>
References: <1140631146.5580.5.camel@Desarrollo>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1140631146.5580.5.camel@Desarrollo>
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.087 required=5 tests=[AWL=0.087]
X-Spam-Score: 0.087
X-Spam-Level: 
X-Archive-Number: 200602/273
X-Sequence-Number: 24283

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



From smarlowe@g2switchworks.com Wed Feb  22 21:16:27 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 5FC499DC86E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 17:16:27 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 81051-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 17:16:28 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from exchange.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 4A0EE9DC832
	for <pgsql-sql@postgresql.org>; Wed, 22 Feb 2006 17:16:24 -0400 (AST)
Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com
	([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ;
	Wed, 22 Feb 2006 21:16:26 +0000
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	22 Feb 2006 15:16:26 -0600
Subject: Re: Update in all tables
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20060222211348.GE8741@phlogiston.dyndns.org>
References: <1140631146.5580.5.camel@Desarrollo>
	<20060222211348.GE8741@phlogiston.dyndns.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1140642986.5777.39.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Wed, 22 Feb 2006 15:16:26 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.156 required=5 tests=[AWL=0.155,
	UNPARSEABLE_RELAY=0.001]
X-Spam-Score: 0.156
X-Spam-Level: 
X-Archive-Number: 200602/274
X-Sequence-Number: 24284

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.



From ojacobson@osl.com Wed Feb  22 21:25:51 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id B356D9DCAD6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 17:25:51 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 83871-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Feb 2006 17:25:52 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from nelson.osl.com (216-21-133-120.ip.van.radiant.net
	[216.21.133.120])
	by postgresql.org (Postfix) with ESMTP id 127DE9DCA42
	for <pgsql-sql@postgresql.org>; Wed, 22 Feb 2006 17:25:48 -0400 (AST)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
Subject: Re: Update in all tables
Date: Wed, 22 Feb 2006 13:25:48 -0800
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC233D2F@nelson.osl.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] Update in all tables
Thread-Index: AcY39UcTRWoD4IpNSv2Pj1auPGOKOAAALxYg
From: "Owen Jacobson" <ojacobson@osl.com>
To: <pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.129 required=5 tests=[AWL=0.129]
X-Spam-Score: 0.129
X-Spam-Level: 
X-Archive-Number: 200602/275
X-Sequence-Number: 24285

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).
>=20
> 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