Date: Sun, 1 Feb 2004 21:46:26 -0500
From: postgres@jal.org
To: treeml <treeml@itree.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: update more than 1 table (mysql to postgres)
Message-ID: <20040202024626.GC29120@clueinc.net>
Mail-Followup-To: treeml <treeml@itree.org>, pgsql-sql@postgresql.org
References: <AOEMLAIJIPAIPHJEAHBOCEHJDPAA.treeml@itree.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <AOEMLAIJIPAIPHJEAHBOCEHJDPAA.treeml@itree.org>
User-Agent: Mutt/1.3.28i
Organization: clue inc

On Fri, 30 Jan 2004, treeml wrote:

> I am migrating from MySQL to Postagres. I have problem with postgres
> updating 2 tables with one statement.
> 
> In MySQL I can update 2 tables (parent, child) with a statement like this
> 
> UPDATE parent LEFT JOIN child ON parent.pid = child.foreign_key SET
> parent.field1 = 'company',
> child.field2 = 'john'
>  WHERE child.pid = 7
> 
> Or I can also do
> UPDATE parent, child SET parent.field1 = 'company', child.field2 = 'john'
> WHERE
> parent.pid = child.foreign_key
> AND child.pid = 7
> 
> 
> But I couldn't do that in Postgres,
> Only one table is allowed in an update statement. I tried to create a view,
> and updating the view,  but that was not allowed.   I could do 2 SQL
> updates, but I am sure there is a better way to do this.  Anyone have any
> idea. Appreciated.

You can use a transaction:

begin;
update parent set ...;
update child set ...;
commit;

Or if you want to use a rule, you can define a rule to do it:

create or replace rule my_view_update_rule as
	on update to my_view do instead (
...

-j

-- 
Jamie Lawrence                                        jal@jal.org
"Perhaps the truth is less interesting than the facts?" 
   - Amy Weiss, Senior Vice President of Communications, RIAA