PostgreSQL SQL - Update in all tables

Author

Update in all tables

Judith

2006-02-25, 9:48 am

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Janning Vygen

2006-02-25, 9:48 am

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Scott Marlowe

2006-02-25, 9:48 am

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links