Hi All, On Tue, Aug 4, 2009 at 11:27 PM, Sheeri K. Cabral<[email protected]> wrote: > Have you found that multi-update/delete is used to actually change > information in more than one table? All the implementations of *that > usage* that I've seen in the wild have been instead of using foreign > keys and cascading properly.......
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). Now, I am not in the position to check this myself, but if it is true, this syntax isn't quite as useful anymore - I mean, it's still nice syntactic sugar, but it suggests something it isn't, which is evil. So, to me, having multi-table UPDATE/DELETE is nice, but if you have decent support for subqueries, and can assume transactions, it isn't a big deal to lose it. I can imagine that in some scenarios, a single multi-table DELETE/UPDATE can get you some extra performance as compared to multiple statements wrapped in a transaction, but I have no benchmark data to indicate how much. -- Roland Bouman http://rpbouman.blogspot.com/ Author of "Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL", http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
