List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 23 1999 12:18pm
Subject:Re: Detecting when an update found a row but changed no data.
View as plain text  
>>>>> "Martin" == Martin Ramsch <m.ramsch@stripped> writes:

Martin> On Wed, 1999-09-22 16:14:51 -0700, Scott Hess wrote:
>> Martin Ramsch <m.ramsch@stripped> wrote:
>> > My suggested syntax for such a beast is to use the existing REPLACE
>> > syntax, with a new special symbol for "the existing value"!
Martin> [...]
>> > This special symbol only would be allowed for non primary key fields!
Martin> [...]
>> It would be sufficient to specify a unique key [...]

Martin> uh, right, I didn't think of other key candidates.  So the rule could
Martin> be extended (and put another way): there have to be values for at
Martin> least the primary key fields, or one unique key, mutually exclusiv.
Martin> (As you wrote, values in serveral unique fields might be ambiguous
Martin>  and checking for this ambiguity maybe is too expensive ...)

>> Another option to get this behaviour would be something like:
>> 
>> REPLACE INTO mytable SELECT <primary-key>, <new-value>, col1, col2
> FROM
>> mytable WHERE primary-key=<primary-key>

Martin> Definitely the most versatile syntax! :)

<cut>

The problem is that neither of these syntaxes touches one very
common usage: summary tables:

In these we would like to add a row if there wasn't one with the same
primary key;  If there was an old row, we would like to update some summary
information:

The normal way to do this is:

INSERT INTO sum_table (key_col, sum_column) VALUES("key",100);

and in case of dupplicate key:

UPDATE sum_table sum_column=sum_column+100 where key_col="key";


To summarize:

We would like a command that if there isn't a conflicting row, inserts 
a new row, else it should update the existing row.  The update should
allow one to refer to existing columns in the old row.

How about this:

INSERT INTO foo ("key","sum","count") values ("key",100,1)
  IF DUPLICATE_KEY SET sum=sum+100, count=count+1

MySQL would first try to insert the row; If this doesn't succeeded it
should read the conflicting row and instead execute the UPDATE part.

At least the above is quite readable and would be quite easy to implement.

I assume that in most cases, one doesn't want to update that many
columns if there is already a conflicting row.

Regards,
Monty




Thread
Detecting when an update found a row but changed no data.Scott Hess20 Sep
  • Re: Detecting when an update found a row but changed no data.Martin Ramsch20 Sep
  • Re: Detecting when an update found a row but changed no data.Viren Jain20 Sep
    • Re: Detecting when an update found a row but changed no data.Scott Hess20 Sep
      • Re: Detecting when an update found a row but changed no data.Viren Jain20 Sep
      • Re: Detecting when an update found a row but changed no data.gl321 Sep
    • Re: Detecting when an update found a row but changed no data.(James Briggs)21 Sep
      • Re: Detecting when an update found a row but changed no data.Michael Widenius22 Sep
    • Re: Detecting when an update found a row but changed no data.Scott Hess22 Sep
      • Re: Detecting when an update found a row but changed no data.Michael Widenius22 Sep
    • Re: Detecting when an update found a row but changed no data.(James Briggs)22 Sep
    • Re: Detecting when an update found a row but changed no data.Scott Hess22 Sep
      • Re: Detecting when an update found a row but changed no data.Michael Widenius22 Sep
    • Re: Detecting when an update found a row but changed no data.Scott Hess23 Sep
      • Re: Detecting when an update found a row but changed no data.Martin Ramsch23 Sep
    • Re: Detecting when an update found a row but changed no data.Scott Hess23 Sep
      • Re: Detecting when an update found a row but changed no data.Martin Ramsch23 Sep
        • Re: Detecting when an update found a row but changed no data.Michael Widenius23 Sep
          • Re: Detecting when an update found a row but changed no data.gl323 Sep