Re: update more than 1 table (mysql to postgres)

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