← Back to team overview

drizzle-discuss team mailing list archive

Re: PROPOSAL: Change to the Cursor interface

 

Hi Francesco,

On Sun, Apr 25, 2010 at 2:58 PM, Francesco Riosa <francesco@xxxxxxxxxxxx> 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?

> 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.

> 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.


>
>>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
>>>>
>>>
>>>
>



-- 
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