List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 22 1999 11:48pm
Subject:Re: Detecting when an update found a row but changed no data.
View as plain text  
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"!
[...]
> > This special symbol only would be allowed for non primary key fields!
[...]
> It would be sufficient to specify a unique key [...]

uh, right, I didn't think of other key candidates.  So the rule could
be extended (and put another way): there have to be values for at
least the primary key fields, or one unique key, mutually exclusiv.
(As you wrote, values in serveral unique fields might be ambiguous
 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>

Definitely the most versatile syntax! :)

> OTOH, I suspect this is annoying to implement right.

Hmm, MySQL already has all of this besides one thing: it can't neither
insert nor replace into the same table it selects from.  But I guess
this limitation will be easily overcome not later than when subselects
are possible, because then it should be easy to hold the temporary
result of the select and then do the replace/insert.

Using an explicit temporary table it's possible already now:
  CREATE TEMPORARY TABLE tmp type=heap
     SELECT <primary-key>, <new-value>, col1, col2
     FROM mytable
     WHERE primary-key=<primary-key>;
  REPLACE INTO mytable SELECT * FROM tmp;
  DROP TABLE tmp;

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