Wishing for an in-place REPLACE in MySQL

( 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?

All 3 comments so far, oldest first...

INSERT INTO foo (beep, foo) VALUES (…) ON DUPLICATE KEY UPDATE beep = ‘blah’, foo = foo + 2;


– ask

— comment by Ask Bjørn Hansen on January 17th, 2007 at 2:26am JST (17 years, 2 months ago) comment permalink

Wow, thanks Ask, I obviously hadn’t seen that.

It’s more flexible than what I asked for, but unfortunately, requires more verbosity: if there are 97 fields of which 85 are being updated, you have to repeate the 85 fields both in the INSERT clause and in the UPDATE clause. At least since 4.1.1 you can use VALUES(fieldname) so that you have to repeat only the 85 field names, and not the 85 associated values….

— comment by Jeffrey Friedl on January 17th, 2007 at 9:55am JST (17 years, 2 months ago) comment permalink

I didn’t know about the VALUES(fieldname) thing, very neat. Good reminder for me that you learn something when you help others. 🙂

Yeah, it gets a bit verbose either way – but that’s why you make your program generate that part of the SQL. 🙂

(Actually, I have Rose::DB::Object generate most of my SQL – except of course for those “must-be-really-super-perfectly-fast” situations where I’d also use “ON DUPLICATE KEY UPDATE …” – hello irony).

– ask

— comment by Ask Bjørn Hansen on January 22nd, 2007 at 4:13pm JST (17 years, 1 month ago) comment permalink
Leave a comment...

All comments are invisible to others until Jeffrey approves them.

Please mention what part of the world you're writing from, if you don't mind. It's always interesting to see where people are visiting from.

IMPORTANT:I'm mostly retired, so I don't check comments often anymore, sorry.

You can use basic HTML; be sure to close tags properly.

Subscribe without commenting