Actually, after some more reading I now get the impression that MERGE INTO does allow for the same flexibility in handling UPDATEs differently from the INSERTs, and is in fact even more flexible as it allows a separate set to identify the target records.
On Sun, Apr 25, 2010 at 1:14 AM, Roland Bouman <[email protected]> wrote: > Gah...I replied to Jobin, but forgot to reply to Jay and the list.... > > here goes: > > Hi Jobin, > > On Sat, Apr 24, 2010 at 10:20 PM, Jobin Augustine <[email protected]> wrote: >> is this REPLACE is something so useful?. >> are developers are aware and using it to a good extend? > > In my opinion, it is useful. I don't think there is any statistic on > whether developers are making legitimate use of it. But then, this > information is also not available for any other SQL feature. > >> I don't see it in SQL standards and other planets (Postgres, Oracle,...) > > In the SQL standard, the MERGE INTO statement offers similar (although > not equivalent) functionality > > <merge statement> ::= > MERGE INTO <target table> [ [ AS ] <merge correlation name> ] > USING <table reference> > ON <search condition> <merge operation specification> > > (ISO/IEC 9075-2:2003 (E) 14.9 <merge statement>, page 837) > > It is similar in that matching existing rows are updated, and if there > are no such rows, they will be inserted. > Personally I find the standardese particular dense and hard to > understand, but this wikipedia entry explains it nicely: > > http://en.wikipedia.org/wiki/Merge_(SQL) > > Personally I like MySQL's REPLACE and ON DUPLICATE KEY constructs > better because they don't just merge the data, but allow you a IMO > flexible and natural way to handle the updates differently than the > inserts. But this is besides your point: the standard offers an "Upsert" > construct just like MySQL, but it is called MERGE INTO, and not > REPLACE INTO (or INSERT ... ON DUPLICATE KEY UPDATE) > >> for me, REPLACE is as bad as silent commits (in many planets it happens >> before DDL) > > Ok. Then don't use it :) > >> all these headache may go if we could discontinue support for mysqlism. > > That would postpone the problem until drizzle would add > support for SQL standard MERGE INTO syntax. > In other words, it would need to be solved anyway. > > kind regards, > > Roland > >> >> Thank you, >> Jobin. >> >> >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~drizzle-discuss >> Post to : [email protected] >> Unsubscribe : https://launchpad.net/~drizzle-discuss >> More help : https://help.launchpad.net/ListHelp >> >> > > > > -- > 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 > -- 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
