Fundamentals of databases: UPDATE

From Wikibooks, open books for an open world
Jump to navigation Jump to search

PAPER 2 - ⇑ Fundamentals of databases ⇑


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


UPDATE crooks
SET DoB = '17/12/1939'
WHERE name = 'Geoff'

All the crooks have moved to Canary Wharf, update the table to show this


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


UPDATE crooks
SET DoB = '16/01/1984' ---be careful with dates, they might be using American format
WHERE name = 'Sheila'
AND gender = 'Female'