Now that you know how to insert, delete and select data it’s time to learn how to modify existing data. You may want to modify existing rows in a table for many reasons, for example the data might have changed, or it wasn’t entered correctly in a first place. Using the Countries table from the previous articles we’ll demonstrate how to update table records. If we want to update the population column for the USA, we can use the UPDATE statement below:

UPDATE Countries
SET Population = 298444216
WHERE Country = ‘USA’

The statement starts with the UPDATE keyword followed by the name of the table we are updating. The second line starts with the keyword SET followed by assigning expression in the form of [Column Name] = [New Column Value], in our case Population = 298444216. The third line of the UPDATE statement will specify to update only the record, with Country value of ‘USA’.

In fact we can update the values in more than one column with a single UPDATE statement. The UPDATE expression below will update both the Population and the Capital columns of the USA table record:

UPDATE Countries
SET Population = 298444216, Capital = ‘Washington, D.C.’
WHERE Country = ‘USA’

What happens if we skip the WHERE part of the statement above? If we do that all records in the table will be updated with the new Population and Capital values. Most likely we will not want to do that, so be careful when using UPDATE statements.

The UPDATE statement can be use an existing column value, to update records. For example if you want to increase the Population column of the USA record with one you can simply do the following:

UPDATE Countries
SET Population = Population + 1
WHERE Country = ‘USA’

If you want to increase the Population of all countries with 10%, you can use this SQL statement:

UPDATE Countries
SET Population = Population * 1.10
WHERE Country = ‘USA’

SQL Training

SQL Training > Modifying Data