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