title: simplifying update/insert (upsert) processing in MySQL
body: <p>A common operation is to insert a new row if none exists, or update an existing row. Unfortunately, the syntax for insert and update SQL statements is completely different: Insert takes a list of columns followed by a corresponding list of values, while update takes a list of column=value pairs. The MySQL "insert...on duplicate key update" statement (its upsert statement) doesn't solve this problem, as it still requires the complete insert column/value lists followed by the complete update column/value list. [UPDATE: Comment from Wrikken below points out that the two statements can share the column=value syntax, but the other issues remain.]</p>
  
  <p>Related to this problem is that if you're using triggers to check the data (as I do), you need two triggers (before-insert and before-update), and, as they have to use the "new" qualifier, you have to either write the checking code twice, once for each trigger, or put it into a procedure. If you use a procedure, you have to pass each column as a separate parameter, because the procedure can't use "new", which is a lot of potentially error-prone typing if you have a lot of columns. Each column has to have its type in the create table statement and then a second time in the definition of the checking procedure. Once little mistake and you've created a subtle, hard-to-find bug. I don't like any approach that involves coding the same thing twice. (It's the equivalent of non-normalization.)</p>
  
  <p>Thinking about this insert/update problem, I've been toying with the following idea, and I'd like some feedback, especially if anyone has actually tried it:</p>
  
  <p>Use insert only for a placeholder row, holding only the minimal amount of data, and to get or set the primary key. Then, put all of the user-entered data into an update statement. Now, you don't need "insert...on duplicate key update", as plain update will do. Also, you need to check data only on a before-update trigger, as there is nothing to check on an insert. (All user-supplied data from the entry form is handled by the update, never by the insert.)</p>
  
  <p>The chief disadvantage of this method, of course, is that there are two operations for a new row: insert followed by update, instead of an insert. But, that may not be a factor because:</p>
  
  <ol>
  <li><p>Inserts may be relatively rare. For example, in a student grading application I did a few years ago for the Richardson (TX) School District, only a couple of thousand or so students were added each year, whereas there were tens of thousands of updates, as the teachers used the system throughout the school year.</p></li>
  <li><p>In several other systems I've built, performance was irrelevant. For example, a current system I'm working on has only two or three people updating the database for only a few hours a week. The load is so small that the overhead caused by two operations (insert + update) when only one would suffice is insignificant. (It's only for new rows, remember.)</p></li>
  </ol>
  
  <p>So, has anyone actually tried this: Insert only to create a minimalist, placeholder row, and use update for all user-supplied-data updates?</p>