← Back to team overview

drizzle-discuss team mailing list archive

Re: PROPOSAL: Change to the Cursor interface

 

Hi Roland

Ohh i missed the main point of your reply.

>> all these headache may go if we could discontinue support for mysqlism.

>But that would postpone the problem until you would like to add
>support for the standard MERGE INTO syntax.
>In other words, it would need to be solved anyway.

No. not requied, MERGE is bit different on its use.
(just like its name sounds)

let me explain, the overall syntax of merge will look 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 ...*

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 <jobinau@xxxxxxxxx> 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 <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