Changing Existing Data

In most databases, you'll want to modify data at some point as it becomes out-of-date. This isn't always the case; for example, you wouldn't want to change data in a database containing a list of World Cup-winning snowboarders of recent years, but in many cases it's vital. Allowing customers to update their details in an e-commerce application is essential, for example, or else they would forever be getting orders delivered to houses at which they no longer lived. In SQL you use the UPDATE keyword to do this.

Updating Rows with UPDATE

The basic syntax for UPDATE is as follows:

UPDATE Table SET NewColumnValues WHERE RowFilter;

As before, Table selects the table containing the row (or rows) you want to modify. NewColumnValues is where you provide the new values you want to apply to row(s), and RowFilter allows you to select what row or rows to update, using the same WHERE syntax you saw in the previous chapter.

The main new thing here is NewColumnValues. This part of the statement involves providing a list of comma-separated column names and new values as follows:

UPDATE Table
SET ColumnA = NewValueA, ColumnB = NewValueB
WHERE RowFilter;

The values can be literal values, column names if you want to copy data from other columns, or the results of some calculation. Calculations are covered in detail in Chapter 5, "Performing Calculations and Using Functions," but for now it's worth noting that you can perform tasks such as incrementing column values, adding values based on values in other columns, and so on. As a quick example, the following query would be great if someone were feeling generous and wanted to increase their wife's bank account by a substantial amount to pay for hair-care products:

UPDATE BankAccounts
SET Balance = Balance * 10
WHERE AccountHolder = 'Donna Watson';

Unfortunately, we don't have access to the database we'd need to do this, but the idea is nice.

More typically, you'll change values with literal values obtained as part of some other application, for example:

UPDATE LineProducts
SET ProductName = 'New Improved Plastic Asparagus Tips', ItemsInStock = 1000
WHERE ProductID = 47;

Here a row is identified by its ID and values changed. Note that there's no need to provide values for all the columns in the table; where a new column value isn't specified, the old value will remain after the statement has executed.

MODIFYING ROW DATA

Execute the following code against the InstantUniversity database:

UPDATE Professor
SET Name = 'Prof. ' || Name
   WHERE ProfessorID > 6;

Note that for SQL Server and Access, the || concatenation symbol should be replaced with a +. Also, the syntax for concatenation for MySQL is quite different, and the previous SQL should appear as follows:

UPDATE Professor
SET Name = CONCAT('Prof. ', Name)
   WHERE ProfessorID > 6;

The rows should now have been altered as follows:

   ProfessorID  Name

   -----------  ---------------
   ...          ...
   6            Prof. Hwa
   7            Prof. Snail at work
   8            Prof. John Jones
   9            Prof. Gary Burton
   ...          ...

This example changes the value in the Name column in Professor to a concatenation of 'Prof. ' and the original value of this column. This modification is applied to the 13 new records added to the Professor table in the previous two examples. To filter these rows, we selected all records with a ProfessorID value of more than six.

Note

Care should be taken when using an UPDATE statement— omitting the WHERE clause will result in changes to every row of the table. If in doubt, replace the word UPDATE with SELECT while building your statement to test the results before applying them.

Using UPDATE with Different RDBMSs

Again, different RDBMSs include their own additions to the UPDATE syntax:

SQL Server: Includes the option of having a FROM clause after the SET clause to make updating related tables much simpler. It can also include an OPTION clause that contains optimizer hints used to optimize the way the statement is executed by SQL Server. For example, specifying OPTION FAST n causes SQL Server to optimize the query so that the first n rows are returned as quickly as possible, before the rest of the result set is returned.
Oracle: Oracle allows you to include a RETURNING clause at the end of the UPDATE statement to return the amended rows into a variable.
MySQL: Includes a LIMIT clause to limit the number of rows that will be affected (see the next chapter for more information on this keyword).
DB2: Includes an ONLY clause that can be used to limit the scope of the update to just the table in question and not to any subtables. This clause is only used with typed tables and typed views. These are special DB2-specific constructs that allow you to store object instances and to define the hierarchical relationships between them.

There are also capabilities for dealing with cursors, which you'll look at a little later in the book (in Chapter 9, "Using Stored Procedures"), using the WHERE CURRENT OF cursor syntax in SQL Server, Oracle, and DB2.