SQL UPDATE command

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 ;).

Timeline

Newsletter

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.

Metadata

Tags: , , ,

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.