{"id":336,"date":"2007-01-16T20:49:45","date_gmt":"2007-01-16T11:49:45","guid":{"rendered":"https:\/\/regex.info\/blog\/2007-01-16\/336"},"modified":"2007-01-16T20:49:45","modified_gmt":"2007-01-16T11:49:45","slug":"wishing-for-an-in-place-replace-in-mysql","status":"publish","type":"post","link":"https:\/\/regex.info\/blog\/2007-01-16\/336","title":{"rendered":"Wishing for an in-place REPLACE in MySQL"},"content":{"rendered":"\n

( If you're not familiar with MySQL<\/a>, this post will be of no interest. )<\/p>\n\n

\n\nI like that MySQL has its replace<\/a>\ncommand, which acts like an insert<\/a> except\nthat if the newly-inserted data would cause a conflict<\/span> with one or more unique<\/a>\nkeys, one or more pre-existing rows are automatically delete<\/a>ed to\nmake way for the new data.\n\n<\/p>\n \n

\n\nHowever, it seems that most times that I want<\/span> to use replace<\/span>, I really<\/b> want\nsemantics along the lines of “update<\/a> if already there; insert<\/span> if not.” The only\ndifference is in the value given to unmentioned fields: in normal replace<\/span> semantics, unmentioned\nfields get the default value for the field (the one created, explicitly or\nimplicitly, at the time of table creation). What I want<\/span> is that fields that\nI don't<\/span> give a value<\/span> for on the replace<\/span> line to get the value that\nhad been there before (if the record was already there, and if not, then\nfall back to the default for that field).\n\n<\/p>

\n\nThis seems a totally natural (and useful!) extension, so it's beyond me why\nit's not there. I looked<\/span> around the docs, but couldn't find it.\n\n<\/p>

\n\nAs for the syntax, I can imagine<\/span> either an in place<\/span> option to replace<\/span>, or an or insert<\/span> option to update<\/span>.\n\n<\/p>

\n\nWhat do you think, Monty?\n\n<\/p>\n\n","protected":false},"excerpt":{"rendered":"

( If you're not familiar with MySQL, this post will be of no interest. )<\/p>

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. <\/p>

However, it seems that most times that I want to use replace, I really<\/b> want semantics along the lines of \"update if already there; insert if not.\" The only difference is in the value given to unmentioned fields: [...]","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"_links":{"self":[{"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/posts\/336"}],"collection":[{"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/comments?post=336"}],"version-history":[{"count":0,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/posts\/336\/revisions"}],"wp:attachment":[{"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/media?parent=336"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/categories?post=336"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/tags?post=336"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}