pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate more than 1 table (mysql to postgres)
4+ messages / 4 participants
[nested] [flat]
* update more than 1 table (mysql to postgres)
@ 2004-01-30 08:19 treeml <treeml@itree.org>
2004-02-02 02:46 ` Re: update more than 1 table (mysql to postgres) postgres@jal.org
2004-02-02 03:21 ` Re: update more than 1 table (mysql to postgres) Christopher Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 4+ messages in thread
From: treeml @ 2004-01-30 08:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: update more than 1 table (mysql to postgres)
2004-01-30 08:19 update more than 1 table (mysql to postgres) treeml <treeml@itree.org>
@ 2004-02-02 02:46 ` postgres@jal.org
1 sibling, 0 replies; 4+ messages in thread
From: postgres@jal.org @ 2004-02-02 02:46 UTC (permalink / raw)
To: treeml <treeml@itree.org>; +Cc: pgsql-sql
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 as
on 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: update more than 1 table (mysql to postgres)
2004-01-30 08:19 update more than 1 table (mysql to postgres) treeml <treeml@itree.org>
@ 2004-02-02 03:21 ` Christopher Browne <cbbrowne@acm.org>
2004-02-02 04:26 ` Re: update more than 1 table (mysql to postgres) Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: Christopher Browne @ 2004-02-02 03:21 UTC (permalink / raw)
To: 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 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/;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: update more than 1 table (mysql to postgres)
2004-01-30 08:19 update more than 1 table (mysql to postgres) treeml <treeml@itree.org>
2004-02-02 03:21 ` Re: update more than 1 table (mysql to postgres) Christopher Browne <cbbrowne@acm.org>
@ 2004-02-02 04:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-02-02 04:26 UTC (permalink / raw)
To: pgsql-sql
>> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-02-02 04:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-30 08:19 update more than 1 table (mysql to postgres) treeml <treeml@itree.org>
2004-02-02 02:46 ` postgres@jal.org
2004-02-02 03:21 ` Christopher Browne <cbbrowne@acm.org>
2004-02-02 04:26 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox