← Back to team overview

drizzle-discuss team mailing list archive

Re: PROPOSAL: Change to the Cursor interface

 

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 <roland.bouman@xxxxxxxxx> 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 <jobinau@xxxxxxxxx> 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     : drizzle-discuss@xxxxxxxxxxxxxxxxxxx
>> 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



Follow ups

References