Updating Data in SQL
In terms of updating a record in a database table, the SQL ‘update’ statement needs to be used, which takes the following form.
UPDATE table_name SET column_name='value' WHERE where_condition;
As with the ‘select‘ and ‘insert‘ statements, a comma can be used where more than one field needs to be updated.
UPDATE table_name SET column_name1='value1', column_name2='value2', column_name3='value3' WHERE where_condition;
The ‘where’ condition might be the ‘id’ of a particular record for example. The example below uses the same ‘person‘ table from the previous examples for selecting and inserting data. It updates the ‘lastname’ and ‘title’ for the record with an ‘id’ of 4.
UPDATE person SET lastname = 'Adams', title = 'Mrs' WHERE id = 4;
The contents of the ‘person‘ table, following the update, can now be seen below.
id | firstname | lastname | title | dob |
1 | Fred | Bloggs | Mr | 1980-05-05 |
2 | Simon | Smith | Mr | 1960-04-01 |
3 | Freida | Bloggs | Mrs | 1970-10-12 |
4 | Fiona | Adams | Mrs | 1985-05-19 |
5 | John | Smith | Mr | 1985-10-12 |
An ‘update’ statement can amend more than one record at a time. To use the ‘person‘ table again, if the ‘where’ condition specified ‘lastname’ to be equal to ‘Bloggs’, then two records would be updated.