From: postgres@jal.org
To: treeml <treeml@itree.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: update more than 1 table (mysql to postgres)
Date: Sun, 1 Feb 2004 21:46:26 -0500
Message-ID: <20040202024626.GC29120@clueinc.net> (raw)
In-Reply-To: <AOEMLAIJIPAIPHJEAHBOCEHJDPAA.treeml@itree.org>
References: <AOEMLAIJIPAIPHJEAHBOCEHJDPAA.treeml@itree.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgres@jal.org, treeml@itree.org
Subject: Re: update more than 1 table (mysql to postgres)
In-Reply-To: <20040202024626.GC29120@clueinc.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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