UPDATE and updating

The SQL update command allows you to make specific changes to one or more rows. You could in theory do this by deleting the old one and inserting a new one but this means you have to deal with all the data and can only do one row of data. Using UPDATE solves both of these problems.

Basic syntax
As with everything there is a basic command structure to allow you to sort out what you want and run the various different parts of the commands. The syntax for using UPDATE is as follows:

UPDATE table SET field1 = 'value1', field2 = somenumber WHERE field3 = 'value3'

There are 3 parts to this. The first is the table to update. The second is the values which you are setting. The third is the validation of which rows in the table should have the update run.

Sample table
To make this easier to do I am going to create a sample table of data from which we can work from. The table is going to be called friends and will have several bits of data in it.

Ok first lets say that Josh tells me that his email has changed from happy@killer.com to josh@wiggam.com. So we need to run an update command to change the email address from the old one to the new one.

UPDATE friends SET email = 'josh@wiggam.com' WHERE name = 'Josh'

In this code, all rows where the name is Josh, will have the current email changed to match josh@wiggam.com. This would be a problem if I had two Josh's but I don't so only one is updated. If I did have two Josh's I could change the statement to say:

WHERE email = 'happy@killer.com'

Or I could also try

WHERE name = 'Josh' AND age = 30

Updating multiple values
Next let's pretend that it's Alex's birthday - he turns 24 and decides that his favourite colour is now green. So we need to update two values in the table at once. Luckily this is pretty simple to do.

UPDATE friends SET age = 24, favourite colour = 'green' WHERE name = 'Alex'

First thing I must stress - you should never have a column name with a space it - don't do it. But seen as this is just an example it's not a problem. If you really needed a name like that then using FAVOURITE_COLOUR or a dash, etc to space them out.

In the above update statement both age and favourite colour are updated in every row where the name is Alex. Also there are no hyphens around age as it's a number and so does not require them. You will get an error if you try to put them round a number when using a number column, as you will do for not using them in a text column.

Updating multiple rows
Finally I am going to look at how to update two records at a time. Though we have really already covered it so this is more of a confirmation to make sure you have it right. Take a look at this code:

UPDATE friends SET age = 60 WHERE favourite colour = 'blue'

I decide that everyone who likes blue is an old foogy so I am going to update everyone's age to 60 who said their favourite colour was blue. As usual the script looks for everyone who is ok by the WHERE validation. This time it finds both Jim and Mike and changes both their ages to 60.

Conclusion
The UPDATE command is fairly simple - you just specify the table to update, the values to set and the conditions a row must meet for the update to be run on that row. One final thought though - take another look at Mike's email address and try and guess what music I was listening to when I wrote that one :).

Yep, it was of course Feeder ;).