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.