From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@postgresql.org
Subject: Re: update more than 1 table (mysql to postgres)
Date: Sun, 01 Feb 2004 23:26:22 -0500
Message-ID: <28001.1075695982@sss.pgh.pa.us> (raw)
In-Reply-To: <m3smhutbdv.fsf@wolfe.cbbrowne.com>
References: <AOEMLAIJIPAIPHJEAHBOCEHJDPAA.treeml@itree.org>
<m3smhutbdv.fsf@wolfe.cbbrowne.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: update more than 1 table (mysql to postgres)
In-Reply-To: <28001.1075695982@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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