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

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.

Tree

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

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/>

>> 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

One has to wonder what the above construction is even intended to mean.
Since it's a LEFT JOIN, presumably there can be rows coming out of the
join that have a "parent" but no "child" row.  What does it mean to
update child.field2 when there's no child row?  You could make about
equally good arguments for raising an error, updating the parent side
only, or updating neither.

Even without a LEFT JOIN, I don't understand what's expected to happen
when the same row in one table joins to multiple rows in the other table.

One advantage of following standards is that the standards have usually
been thought through in some detail (though there are crannies of the
SQL spec that hardly seem to deserve that description :-().  This thing
has not been thought through.  I'm sure the actual behavior of the
corner cases in MySQL is just whatever happened to fall out of their
implementation.
        regards, tom lane