Simon, > The UPSERT concept is also supported by Teradata, who simply append an > ELSE INSERT clause onto the standard UPDATE syntax. MySQL REPLACE seems > to me to be a fairly small subset of MERGE functionality and we ought to > be able to offer that functionality as a side branch of the main work.
Yes, I guess my hesitation on the full-table-lock strategy is that it doesn't really fulfill the mandate for why people want REPLACE-like statements ... to give them an INSERT-or-UPDATE with *higher* efficiency and concurrency than doing two statements. That being said, I've personally designed more than a dozen web applications and have not yet been faced with a single circumstance of not knowing whether I wanted to INSERT or UPDATE. I've even ported MySQL apps and found it easy to re-code them to do "if $id = 0, then insert ..." without even needing to use a pl/pgsql hack. So we thus have two seperate use cases. The first, for bulk loading/ETL is what MERGE fulfills rather neatly and for that full table locking is perfectly OK, even desirable. You really don't want to MERGE-load the same table on two threads at once. The second case is for applications coded for MySQL; this is the REPLACE case. However, the most common MySQL applications doing this use full table locking (MyISAM) anyway! So, while full table locking wouldn't gain them any performance over using two statements, it shouldn't lose them anything they're used to having. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly