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 as
on update to my_view do instead (
...

-j

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