Re: update more than 1 table (mysql to postgres) - Mailing list pgsql-sql

From Christopher Browne
Subject Re: update more than 1 table (mysql to postgres)
Date
Msg-id m3smhutbdv.fsf@wolfe.cbbrowne.com
Whole thread Raw
In response to update more than 1 table (mysql to postgres)  ("treeml" <treeml@itree.org>)
Responses Re: update more than 1 table (mysql to postgres)
List pgsql-sql
Oops! treeml@itree.org ("treeml") was seen spray-painting on a wall:
> 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.

Well, what's going on here is that PostgreSQL is conforming to the SQL
standards.  The syntax MySQL is providing is clearly a deviation from
standards, and once you head down the "we're ignoring standards" road,
you cannot have _any_ expectations of things functioning similarly
(or, for that matter, FUNCTIONING AT ALL) with another database
system.

There are several _possible_ solutions to this; which one is
preferable is certainly in the eye of the beholder:

1.  Do two UPDATEs inside a transaction.  Perhaps...
   begin;   update parent set field1 = 'company' where exists (select *     from child where foreign_key = parent.pid
andchild.pid = 7);   update child set field2 = 'john' where pid = 7 and exists     (select * from parent where
parent.pid= child.foreign_key);   commit;
 

2.  You might set up a view that joins the tables, such as 
 create view combination as   select parent.pid as parent_pid, child.pid as child_pid,    parent.field1, child.field2
fromparent, child   where parent.pid = child.foreign_key;
 
 and then create a RULE allowing updates to this view that allows updating whichever fields that it is appropriate to
allowthis on.
 
 This will involve fairly fancy footwork, unfortunately.  It's doable, but there's something of a learning curve...

In either case, there are still two update statements; in the
updatable VIEW situation, they hide a little "out of VIEW" (if you'll
pardon the pun!).
-- 
"cbbrowne","@","ntlug.org"
http://www3.sympatico.ca/cbbrowne/languages.html
Rules  of the  Evil Overlord  #41. "Once  my power  is secure,  I will
destroy all those pesky time-travel devices."
<http://www.eviloverlord.com/>


pgsql-sql by date:

Previous
From: postgres@jal.org
Date:
Subject: Re: update more than 1 table (mysql to postgres)
Next
From: Tom Lane
Date:
Subject: Re: update more than 1 table (mysql to postgres)