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