List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 21 1999 7:51pm
Subject:Re: Detecting when an update found a row but changed no data.
View as plain text  
>>>>> "James" == James Briggs <briggsja@stripped> writes:

James> Scott Hess wrote:

>> _If_ you have all the data for the row, or you don't care about the
>> existing data in columns you aren't setting, then REPLACE works well for
>> this type of thing.  From the manual, 'REPLACE works exactly like INSERT,
>> except that if an old record in the table has the same value as a new
>> record on a unique index, the old record is deleted before the new record
>> is inserted. See section 7.13 INSERT syntax.'
>> 
>> It would be nice to have a variant on REPLACE where it wouldn't delete the
>> existing row, but would instead update provided columns, but still create
>> the row if it doesn't exist.

James> Hear, Hear! REPLACE would be much more useful without the implicit DELETE,
James> or with an option to forego the DELETE.

James> I could cut the number of database operations I do in half with such a
James> feature.

Do you have any suggestions for a general useful syntax for this?

How about:

IF INSERT INTO TABLE (...) VALUES (..) 
THEN  UPDATE SET col1=#, col2=#, col3=#...
END IF

(Note that there is no WHERE part in the update, but this would only
update the first row that conflicts with the INSERT statement)

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