Fundamentals of databases: UPDATE
Database aren't always perfect and there may be times that we want to change the data inside our database. For example in Facebook if someone is annoying you and you limit their access to your profile, you'd update the access field from 'normal' to 'restricted'. Or if one of our crooks gets an additional scar you'd have to update the numScars field. Let's take a look at that example, where our crook Peter gains a scar on his right cheek. This was his initial state:
name: Peter numScars: 7
UPDATE crooks
SET numScars = 8
But we have a problem here, this statement updates all records to numScars = 8. This means that every crook will now have 8 scars!
| ID | name | gender | DoB | town | numScars |
|---|---|---|---|---|---|
| 1 | Geoff | male | 12/05/1982 | Hull | 8 |
| 2 | Jane | female | 05/08/1956 | York | 8 |
| 3 | Keith | male | 07/02/1999 | Snape | 8 |
| 4 | Oliver | male | 22/08/1976 | Blaxhall | 8 |
| 5 | Kelly | female | 11/11/1911 | East Ham | 8 |
| 6 | Marea | female | 14/07/1940 | Wythenshawe | 8 |
We need to specify which crooks we want to update by using a WHERE clause, you saw it earlier in the SELECT example.
UPDATE crooks
SET numScars = 8
WHERE name = "Peter" --only updates those people who are called Peter
|
Exercise: UPDATE statements Using the above data set, write an UPDATE statement to change Geoff's DoB to 17/12/1939 Answer: UPDATE crooks
SET DoB = '17/12/1939'
WHERE name = 'Geoff'
All the crooks have moved to Canary Wharf, update the table to show this Answer: UPDATE crooks
SET town = 'Canary Wharf'
All the female crooks named Sheila have lied about their ages and they were all born on January 16, 1984 Answer: UPDATE crooks
SET DoB = '16/01/1984' ---be careful with dates, they might be using American format
WHERE name = 'Sheila'
AND gender = 'Female'
|