← Back to team overview

drizzle-discuss team mailing list archive

Re: PROPOSAL: Change to the Cursor interface

 

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

Follow ups

References