SQL UPDATE

The SQL UPDATE clause serves to update data in database table. The SQL UPDATE clause basic syntax looks like this:

UPDATE Table1 SET Column1 = Value1, Column2 = Value2,

The first line of the above SQL UPDATE statement defines which table we are updating. The second line starts with the SET SQL keyword followed by one or more Column = Value pairs separated by commas. The second line of the UPDATE statement defines which table columns to update and with what value.

Please consider the following SQL UPDATE syntax:

UPDATE Weather SET AverageTemperature = 20

Before we run this UPDATE SQL expression, our Weather table looks like this:

CityAverageTemperatureDate
New York22 C10/10/2005
Seattle21 C10/10/2005
Washington20 C10/10/2005
New York18 C10/09/2005
Seattle20 C10/09/2005
Washington17 C10/09/2005

After the update it looks like this:

CityAverageTemperatureDate
New York20 C10/10/2005
Seattle20 C10/10/2005
Washington20 C10/10/2005
New York20 C10/09/2005
Seattle20 C10/09/2005
Washington20 C10/09/2005

As you can see all values in the AverageTemperature column were set to 20. But what if we want to update (change) only the AverageTemperature values for New York? We can do that by using the UPDATE and the WHERE SQL clauses together:

UPDATE Weather SET AverageTemperature = 20 WHERE City = 'New York'

The result will be:

CityAverageTemperatureDate
New York20 C10/10/2005
Seattle21 C10/10/2005
Washington20 C10/10/2005
New York20 C10/09/2005
Seattle20 C10/09/2005
Washington17 C10/09/2005

In some cases you might want to UPDATE a column in a table, and make the new value of the column dependable on the old one. For example you might want to increase the AverageTemperature column values with 5 C for all entries in the table. To do this kind of SQL UPDATE you can use the following UPDATE statement:

UPDATE Weather SET AverageTemperature = AverageTemperature + 5

Our SQL UPDATE statement above simply instructs says that the new value of AverageTemperature will be equal to the old one plus 5.

The SQL UPDATE clause is very powerful and you can easily alter one or more table entries by mistake, thus losing their original values. To avoid that make sure you update only the rows that you want, by utilizing the SQL WHERE clause. It's a good idea to make a backup of your table before running UPDATE statements for tables with important data.