SQL 101 – UPDATE

Previously, I showed you how to get new information into a SQL database.  This time I’ll show you how to make changes to that data, once it’s in there.  The basic structure of the change command is:

UPDATE tableName SET
   columnName = 'value'
WHERE
   <some test>

I can’t tell you how important that WHERE clause is.  Grab the example file here,  and let me illustrate how important the WHERE clause is.  If you ran

UPDATE productSale SET
 purchaseDate = '1/1/2005'

Instead of this:

UPDATE productSale SET
purchaseDate = '1/1/2005'
WHERE
productname = 'paper'
and qtypurchased = 200
and purchaseDate = '1/1/2050'

I think you would be very upset when you saw all four records in your table.  You would find all of them now have the purchaseDate ‘1/1/2005’, when you may have only mean to update the one record at the end.  This is the main reason I’ll introduce you to TRANSACTION very soon.  It can help serve as a safety net when inserting, updating, and deleting records in tables.

For now, I want you to be very careful, and if you update too many records there I want you to know pain can be a very effective teacher.  I’ll be honest, I’ve forgotten a WHERE clause more than once.  I’ve had to make a call to the DBA to get a database restored.  I’ll also tell you as a result I tend to double check my code especially when dealing with production tables.

Speaking of which, have I told you my problem with working directly on production tables?  Especially when you don’t use a test environment first?  If not, I’ll write up a rant, and post it online soon!  I just want you to know testing before doing is as important as following these steps when making an update.

1. SELECT the update.  Before running an update,  run a SELECT statement with your where clause to make sure you’re only getting the records you expect.
2. BEGIN a transaction.
3. Run the UPDATE statement
4. SELECT the changes, verify you only changed the records you think you did.
5. COMMIT the transaction.

I’ll show you how to use TRANSACTIONs in a post very soon, until then, step 1 will be very important to you!

If you have any questions, please send them in.  I’m here to help you learn more about SQL, and the only way I’ll know what to share, is if you tell me what you need to know!

Previous: INSERT

Next: TRANSACTION