Data Updation

Stored data is rarely final. We may need to make changes in the value(s) of one or more columns of existing records — an address changes, a phone number is replaced, a spelling mistake in a name is discovered. The UPDATE statement is SQL's tool for making such modifications in existing data.

Its syntax:

UPDATE table_name
SET attribute1 = value1, attribute2 = value2, ...
WHERE condition;

The SET clause lists which column(s) receive which new value(s); the WHERE clause states which record(s) the change applies to. That division of labour — SET describes the change, WHERE describes the target — is the whole grammar of the statement.

Updating one column of a particular record

In the STUDENT table, the student with roll number 3 has NULL stored as the GUID (the guardian's ID). Suppose the students with roll numbers 3 and 5 are siblings — they share the same guardian — so the GUID of roll number 3 must be filled in as 101010101010. The record to be changed is pinned down with the WHERE clause:

UPDATE STUDENT
SET GUID = 101010101010
WHERE RollNumber = 3;

MySQL confirms what happened:

Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

That feedback line deserves a glance every time: it reports how many rows the WHERE condition matched and how many were actually changed. The updated data can then be verified by running:

SELECT * FROM STUDENT;

Updating more than one column at once

A single UPDATE statement can modify several columns of the same record(s): the assignments are listed after SET, separated by commas. Suppose the guardian with GUID 333333333333 (Danny Dsouza) has requested that both the address and the phone number on record be changed in the GUARDIAN table:

UPDATE GUARDIAN
SET GAddress = 'WZ - 68, Azad Avenue, Bijnour, MP', GPhone = '9871234567'
WHERE GUID = '333333333333';

Again MySQL reports one row matched and one changed, and a SELECT * FROM GUARDIAN; afterwards shows that guardian's row carrying the new address, with every other row untouched. …