4. Updating a table row

Now that the table exists and has at least one row (record), you can use the sql UPDATE command to change a record.

Carrying on with the example from the previous page, we now want to alter the existing record containing 'The Pullman Club' and to change it to 'The Truman Club'

The existing row is shown below where the record is identified by the primary key ClubID = 1

To do this the following sql command is issued

UPDATE `test`.`clubs` SET `ClubName` = 'The Truman Club' WHERE `clubs`.`ClubID` =1;

The nicely formatted MySQL command is shown below

Breaking this down step by step

The first part is the sql command UPDATE followed by the fully defined table 'clubs'

UPDATE `test`.`clubs` ....

Then comes the word SET to identify the fields to be updated

UPDATE `test`.`clubs` SET `ClubName` = 'The Truman Club' ...

then comes the extremely important WHERE clause which pins down exactly which records are to be updated

UPDATE `test`.`clubs` SET `ClubName` = 'The Truman Club' WHERE `clubs`.`ClubID` =1;

In this case the WHERE clause is declaring that only the record whose primary key is ClubID =1 is to be updated. This means only one record will be changed.

In summary, the generic format of the UPDATE command is

UPDATE {table} SET {field value = new value} WHERE {selection criteria}

Practical advice: If you get the WHERE bit wrong in an update query, then you could easily overwrite every record! On a production server with a live database, it is advisable to use the SELECT query first (see next page) to check out what the WHERE criteria returns - Select is safe because it does not alter any records. Once you are confident with the selection criteria then you can use the update command.