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

Oops! treeml(at)itree(dot)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/>