List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 22 1999 10:02pm
Subject:Re: Detecting when an update found a row but changed no data.
View as plain text  
On Wed, 1999-09-22 13:18:24 -0700, Scott Hess wrote:
> Michael Widenius <monty@stripped> wrote:
[...]
> >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.
> 
> I agree that using REPLACE NODELETE might be confusing.  The only reason I
> suggested it was because the functionality is basically like REPLACE, but
> somewhat modified.  It could equally well be a variant of UPDATE or a
> completely new keyword.  But the INSERT/REPLACE syntax does seem more
> appropriate than the UPDATE syntax, to me, because it seems like having a
> WHERE clause would make things really confusing.

What I miss from this discussion so far is the mention of primary keys!

For all of these commands, be it INSERT, REPLACE, or UPDATE, it has to
be exactly clear which rows are affected!
- INSERT: here it's easy, because _no_ exting row is affected.
- REPLACE: quite easy, too, because one has to specify _all_ fields
          of the row to be replaced or inserted.  This obviously also
          includes the key fields, and so in case of a replace it's
          clear exactly which rows have to be deleted first.
- UPDATE: here a WHERE clause is necessary to specify the rows to
          be updated, because in general this is not deducable from
          the list of SET field=values expressions only.

As far as I understood the discussion, the application of the new
wished command is:
1. we only have values for some of the fields,
   not for the complete row
2. if there already exists such a row,
   we want to update the given fields,
3. otherwise we want to create a new rwo with these values

My question: "such a row"?  What is this?

This question only makes sense to me, if the given values at least
include the primary key so that it's clear to which row(s) actions
refer!  Then it's much clearer:
1. we have a primary key and some additional values
2. we want to update these additional values of the row specified with
   the primary key, if such a row already exists
3. otherwise a row with the given primary key, the given additional
   values and default values elsewhere should be inserted

Better. :)

My suggested syntax for such a beast is to use the existing REPLACE
syntax, with a new special symbol for "the existing value"!

Reasonable candidates seem to be the asterisk (*) or maybe NOC (no
change), NOP (no operation), OLD or KEEP (for keep old value), or
SAME ...

This special symbol only would be allowed for non primary key fields!

Example:
  CREATE TABLE line (
     from CHAR(10) NOT NULL,
     to   CHAR(10) NOT NULL,
     color INT default 0,
     style INT default 0,
     PRIMARY KEY (from,to)
  )
  /* Think of some CAD program that wants to store lines. */
  INSERT INTO line VALUES ('A','B',1,3);
  INSERT INTO line VALUES ('A','C',2,3);

Now I want to set the style of the line from 'B' to 'C' to 5, changing
an existing line, or inserting a new one if necessary:

  REPLACE INTO lines VALUES ('B','C',*,5);
                                    ^^^
Because there still is no such line, this would act equivalent to
  REPLACE INTO lines VALUES ('B','C',NULL,5);

Now I want to set the style of the line from 'A' to 'B' to 5, changing
an existing line, or inserting a new one if necessary:

  REPLACE INTO lines VALUES ('A','B',*,5);
                                    ^^^
Because this line already exists, the symbol * is substituted by the
existing value of this field, and this is eqivalent to:
  REPLACE INTO lines VALUES ('A','B',1,5);

Trying to issue
  REPLACE INTO lines VALUES ('A',*,4,4)l
would result in an error like:
  * not allowed in primary key fields!

Special rules are necessary for tables that don't have a primary key:
I think, then it's best to disallow the * at all!


What do you think of these suggestions?

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
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