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

sql table


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

sql update command

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.


Challenge see if you can find out one extra fact on this topic that we haven't already told you

Click on this link: sql update command





Copyright © www.teach-ict.com