>Hi Francesco, > >On Sun, Apr 25, 2010 at 2:58 PM, Francesco Riosa <[email protected]> wrote: >> Just wanted to tell that many applications I write work exactly with this >> logic, check if a record exist, than go with an insert or an update, there are >> however a two important points which maybe the MERGE syntax does not cover: >> 1) when an insert happen I need the primary key used (in my case a 64bit id) > >You mean, you need it for subsequent statements or program logic (as >opposed to, in the current row of the MERGE statement)? >If that is the case, then I agree, that would be very useful, but I >don't think there's any MySQL syntax that support it. >In other words, that would be a completely new functionality, no?
yes it's mean for program logic, and yes it would be a completely new functionality, currently I do it with a pair of python/php functions. > >> 2) for many insert a row is considered to be duplicate even if there are no >> collision with unique indexes (seem to be satisfied by the previous example) > >Well, I think that this is something that MERGE can solve, because you >control the match condition yourself explicitly (in the ON clause). >REPLACE INTO or ON DUPLICATE KEY UPDATE have a more limited matching >condition, that requires one of the unique constraints or primary key >to match. exactly, tough it could be solved for current syntax by the "NO ENFORCE" index property introduced by SQL 2011? first seen in http://petereisentraut.blogspot.com/2010/04/news-from-sql- standard.html > >> to be really useful in these cases the sintax would be something like: >> MERGE INTO table_name USING table_name ON (condition) >> WHEN MATCHED THEN >> *UPDATE SET column1 = value1 [, column2 = value2 ...]* >> WHEN NOT MATCHED THEN >> *INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...* >> ALWAYS SELECT (column1 [, column2 ...]) >> >> does it make sense? > >Do you mean, the ALWAYS SELECT would return the matched set as it >looks like after the INSERT/UPDATEs have taken place? (which would >include all values as they are stored in the table, including any >generated values caused by auto_increment columns, column level >defaults, and, in case drizzle would support them, timestamp columns >and triggers) > >If so, then yes, that makes sense to me. Yes the SELECT should return values from the matched row _after_ it has been (eventually) modified or inserted > > >> >>>As we can see merge works like a sub query fashion. and UPDATE and INSERT >>>are part of it (3rd and 5th line). >>>so in effect MERGE statement splits into a SELECT statement (1st line) >>>followed by UPDATE or INSERT. >> >>>Here the advantage is, UPDATE and INSERT are explicit. so no ambiguity >>>remains. >> >>>But this is not the case of REPLACE, need a investigation of the return pair >>>to understand actually what happened inside. >> >>>Am i right? >> >>>Thank you, >>>Jobin. >> >>>On Sun, Apr 25, 2010 at 10:24 PM, Jobin Augustine <[email protected]> wrote: >> >>>> Hi Roland, >>>> >>>> Yes!. MERGE can be used as a replacement for REPLACE. :) >>>> but in practical, it is difficult to write REPLACE using MERGE. >>>> i hope nobody will do that, just give a try. i bet you will give up :) >>>> >>>> MERGE statement is designed to serve other purposes very efficiently. >>>> where we deal with multiple tables. >>>> For example. >>>> >>>> 1. Warehousing / Reporting systems require "merging" data from normalized >>>> transaction tables into different models >>>> 2. Archiving old data, merge new data into your archive tables very >>>> efficiently. >>>> >>>> I am not against any extra features like REPLACE. >>>> but just conscious about the extra complexity it adds to the code bases and >>>> propages to other plugins (yet to born ones also) >>>> more complexity means more bugs. >>>> referring above proposal, insertRecord returning a pair ! >>>> >>>> Thank you, >>>> Jobin. >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> On Sun, Apr 25, 2010 at 1:53 PM, Roland Bouman >> <[email protected]>wrote: >>>> >>>>> 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)<http://en.wikipedia.org/wiki/Merge_%28SQL%29> >>>>> > >>>>> > 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<https://launchpad.net/%7Edrizzle-discuss> >>>>> >> Post to : [email protected] >>>>> >> Unsubscribe : https://launchpad.net/~drizzle- >> discuss<https://launchpad.net/%7Edrizzle-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 Thanks for taking the time to explain/extend better the original explanation _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
