>>>>> "Scott" == Scott Hess <scott@stripped> writes:
Scott> Michael Widenius <monty@stripped> wrote:
>> >>>>> "Scott" == Scott Hess <scott@stripped> writes:
Scott> How about just keeping the exact current REPLACE syntax, and add
Scott> or something (REPLACE NODELETE ...).
>> The problem with the above syntax is that its not general enough.
>> It doesn't handle the cases where you want to have different default
>> values when you insert a new row
Scott> Mostly because in such cases, I'd probably want to do the full regime,
Scott> doing the select to see if the row exists, then chosing update versus
Scott> insert based on the results.
Wouldn't it be better to have a syntax that can fix most cases...
>> or when there is more than one conflicting row in the result table.
Scott> ??? As I understand REPLACE, that's never a consideration, because it can
Scott> only affect one row. I thought that was the point. Am I really missing
Scott> something, here?
REPLACE can affect many rows.
REPLACE works by removing all conflicting rows. If you have two
UNIQUE keys, two rows may be removed from the table.
(I know that this isn't that common, but it may happen...)
>> In many cases you may also want to
>> increment something when there is a already a matching row in the
>> If we added the above, we would still have to add another syntax to
>> handle the more general cases.
Scott> My desire for something like REPLACE NODELETE is specifically to address a
Scott> common idiom I see. I _don't_ want to group every single insert/update I
Scott> might ever do under one uber-command! Presumably, REPLACE was added to
Scott> address the relatively common need to insert a row if not present or update
Scott> the existing row if present. The problem with REPLACE is that it can only
Scott> be used if you can provide values for every field you care about in the
Scott> row. I'd just like REPLACE extended so that you can have it _UPDATE_ the
Scott> fields, rather than do a DELETE/INSERT.
>> The meaning of replace gets also very 'vague' with REPLACE NODELETE,
>> its like you are saying 'don't really do a replace'. In this case it
>> would be better with another keyword or something like
>> 'INSERT OR UPDATE table_name'
Scott> I don't follow. REPLACE currently means "Replace the specified row with
Scott> this row." REPLACE NODELETE would mean "Replace the given columns in the
Scott> specified row with these values." INSERT OR UPDATE sounds like just
Scott> another variant of REPLACE. I could vaguely see if you instead suggested
Scott> UPDATE OR INSERT, because that makes it sound like a variant on UPDATE,
Scott> which it somewhat is.
I only think that if you use 'REPLACE NODELETE', it will not be that
clear for a novice what one meant. I would prefere to have a better
keyword/syntax for this.
Scott> One difference from UPDATE that makes what I want more like REPLACE is that
Scott> I want it to apply to one, and only one, row, and thus would expect one or
Scott> more of the SET values to specify a unique key. Specifically, I do not
Scott> want a WHERE clause (in fact, I think a WHERE clause would probably make it
Scott> hard to understand the statement's consequences - it would be the UPDATE
Scott> version of a LEFT JOIN, letting you update rows that don't exist). What I
Scott> want to communicate is basically "Update the single row in the following
Scott> way, or insert it if absent". I don't think I would ever want to say
Scott> "Update all rows of this sort, or insert a new row if there are none".
Scott> Usually in cases like that, I would either just always insert the new row
Scott> regardless of whether there are existing rows, or delete the existing rows
Scott> and insert new replacements.
I understand the problem; It would only be nice to have one common
syntax that solves the following more general problem:
If the row doesn't exists, insert a new, else update it.
The difference from REPLACE is that when you update it, you may want
to access the old values in the update part