On Aug 5, 2009, at 9:45 AM, Roland Bouman wrote:
Can't say about real-life usage, but here's an example of something
that you can't do with foreign keys, and which does make sense
logically:
UPDATE City
INNER JOIN Country
ON City.countrycode = Country.code
SET City.population = City.population + 100
, Country.population = Country.population + 100
WHERE Country.name = 'Netherlands'
AND City.name = 'Amsterdam'
;
Now I would agree that in this example, it is quite easy to do it in a
transaction with two UPDATE statements, because it is easy enough to
point out the right rows in both tables using a simple where. In cases
where the dependent set has multiple rows, you would need a subquery
to point out the right rows to update, which would make things
slightly more complicated
On Wed, Aug 5, 2009 at 12:19 AM, Mark Leith<[email protected]> wrote:
Doing everything as a single unit in this case, does indeed make
more sense than not when you can not be sure of the transactional
and/or referential integrity capabilities of engines, imho.
Anyway, my thoughts about this syntax are that it is pretty cool, but
the MySQL implementation is not.
The thing is, I always figured that this syntax was added to MySQL for
two reasons:
1) to work around the lack of subqueries in the olden pre 4.1 days
2) to allow atomic statements on multiple non-transactional tables,
for example to emulate CASCADE functionality due to lacking foreign
keys.
However, at some point I checked my assumptions with Konstantin
Osipov, and he told me that there is no guarantee the two tables are
updated atomically (at least not for MyISAM).
A solution for (1) above is a lot more important than (2), since for
(2) we have transactions (and if not, no guarantee anyway that the
operation is atomic).
I suspect that it would be _relatively_ easy to allow a join in an
UPDATE statement, while only allowing one of the tables to be updated.
The question is whether this code can be added back to Drizzle without
introducing a new execution path.
The alternative is indeed to use sub-queries, but it may take even
longer before we have a good implementation of sub-queries.
Personally I think Drizzle should throw out sub-queries (in the WHERE
condition) altogether. After all, practically any sub-query can be
formulated as a join, so why have 2 ways of doing the same thing?
--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
