From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: update more than 1 table (mysql to postgres)
Date: Sun, 01 Feb 2004 22:21:00 -0500
Organization: cbbrowne Computing Inc
Lines: 66
Message-ID: <m3smhutbdv.fsf@wolfe.cbbrowne.com>
References: <AOEMLAIJIPAIPHJEAHBOCEHJDPAA.treeml@itree.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Reasonable Discussion,
	linux)
Cancel-Lock: sha1:29wVSUvdD/YHR3kfyrgdUzVrBig=
To: pgsql-sql@postgresql.org

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 and child.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 
   from parent, 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 allow this 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/>