( If you're not familiar with MySQL, this post will be of no interest. )

I like that MySQL has its replace command, which acts like an insert except that if the newly-inserted data would cause a conflict with one or more unique keys, one or more pre-existing rows are automatically deleteed to make way for the new data.

However, it seems that most times that I want to use replace, I really want semantics along the lines of “update if already there; insert if not.” The only difference is in the value given to unmentioned fields: in normal replace semantics, unmentioned fields get the default value for the field (the one created, explicitly or implicitly, at the time of table creation). What I want is that fields that I don't give a value for on the replace line to get the value that had been there before (if the record was already there, and if not, then fall back to the default for that field).

This seems a totally natural (and useful!) extension, so it's beyond me why it's not there. I looked around the docs, but couldn't find it.

As for the syntax, I can imagine either an in place option to replace, or an or insert option to update.

What do you think, Monty?