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