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 three 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.
Name email age favourite colour Jim jim@mail.com 24 blue Alex alex@operamail.com 23 black Josh happy@killer.com 30 grey Mike mikebob@avril.com 27 blue
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 ;).
Don't have time to check my blog? Get a weekly email with all the new posts. This is my personal blog, so obviously it is 100% spam free.
Tags: data, sql, update, updating
This entry was posted on Sunday, September 16th, 2007 at 1:44 pm and is filed under Life, Tech. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.