Posted on

mysql: writing a save query, with a twist

something i’ve had to do in almost every application i’ve developed is create a “save” query. the save query is just a command query that tells mysql to save this record using a specific id, but update the table using the rest of the information if a record with that id exists already.

here’s an example:

insert into myTable(id,f1,f2,f3) values(?,?,?,?)
on duplicate key update f1=?, f2=?, f3=?;

this can save a lot hassle trying to figure out in your app if the record is a new one or if one we have already should be updated. there are a couple of things that need to happen in order for this to work, though. first, your mysql table needs to have an auto incrementing primary key. next, your app needs to always post a record id. the record id posted should be ‘0’ for a new record or the valid id for an existing record. you could also post an arbitrary id that doesn’t exist, but i can’t see an application for that, and you would run the risk of colliding with an actual record.

part of the magic of this is in the saving of new records. when you have an auto increment primary key as your id field and you make an insert with a ‘0’ for that key, it auto-magically creates a new record id that is one higher than the highest one already in there.

the other cool part of that query is the update part. on duplicate key update tells the database that if the id exists already, then update the record with that id using the following information. so in one swift statement, you can save a new record or update an existing one, and have simplified what it is you need to post from your application to do so.

this works especially well in ajax applications. in fact, there is even a way to get the server to send back the newly created record id so that your app can update the record again should it need to without a whole lot of effort. you can read about that in an older article i wrote.

all of that is great and awesome, but it gets even cooler than that. there is a way to make sure that information that needs to stay as is will stay that way unless it meets your criteria for change. mysql provides a “Case” method for this. its more or less a switch statement that can be used in selects, updates, and inserts. in this example, we’re going to use it in the update clause of our save query:

insert into myTable(id,f1,f2,f3) values(?,?,?,?)
on duplicate key update f1=?, f2=?,
f3=(case when f3 != '' then f3 else ? end);

on line 3 you can see that we set f3 to the result of the case statement. in this example, the case statement looks to see if f3 is empty. if it is, it will put our new information into it. if there’s something there, it will leave it alone. you could also use other fields in the case statement and make any number of comparisons. for instance, you might only want to update f3 if f6 is set to ‘true’.

the case statement has five main parts:

  1. case: declares that we are using a case statement
  2. when: is always followed by some kind of expression that will resolve to either true or false. usually this is a comparison of some type.
  3. then: this is the value you’ll get if the when resolved to true
  4. else: this is what you’ll get if when is false
  5. end: this declares the end of the case statement.

but where would you use something like this? lets say your application has the requirement of keeping track of who created what records. this is a great way to incorporate “created by” meta-info into your application’s table. now you can leave the created-by info in a field in your app and not worry about deciding whether or not to post it or checking to see if its there already or any of the other hassles. the query does all of this for you. this example specifically will only write it once and will not update it if something is already there.

you can read more about the case statement here.

Leave a Reply

Your email address will not be published. Required fields are marked *