Updating Existing Data

only for RuBoard

The UPDATE statement is used to update existing data in a database. For example, if you want to change the company name in the Suppliers table you can run an UPDATE statement to edit it. Listing 4.4 shows the basic structure of an UPDATE statement.

Listing 4.4 The Basic Structure of an UPDATE Statement

 UPDATE         {          table_name WITH ( < table_hint_limited > [ ...n ] )           view_name           rowset_function_limited         }         SET         {  column_name = {  expression  DEFAULT  NULL }          @variable = expression          @variable = column = expression }  [ ,...n ]     {  {  [ FROM {  < table_source > }  [ ,...n ] ]         [ WHERE             < search_condition > ] }                  [ WHERE CURRENT OF         {  {  [ GLOBAL ] cursor_name }   cursor_variable_name }         ] }         [ OPTION ( < query_hint > [ ,...n ] ) ] 

The UPDATE statement is very powerful, and I recommend that you look over your statement a couple of times before executing it so you don't change data that you don't mean to. Take the following UPDATE statement, for example:

 1: UPDATE Products SET UnitsInStock = 50 

When this statement is executed, it will change every row in the UnitsInStock column of the Products table to be equal to 50. I don't know of anyone who hasn't accidentally done this and changed all the data in a table.

The following statement states that you're going to update the Products table and set the UnitsInStock column to 50. You use conditions to narrow the amount of data changed during the UPDATE ; you can use the same things you used in a SELECT statement: WHERE , SubQueries , LIKE , etc. The best way to minimize mistakes is to use the WHERE clause against the primary key column of the table; for example, if you are updating a table in which the primary key is a product number you would call the UPDATE TABLE SET ? = ? WHERE ProductNumber = ? . Go ahead and UPDATE the UnitsInStock table using the following code. When executed it will add 50 new units to the product named Alice Mutton:

 1: UPDATE Products 2: SET UnitsInStock = 50 3: WHERE ProductName = Alice Mutton 

You've narrowed down the number of rows you're changing to those that have a value of Alice Mutton in the ProductName column. If you are hand coding these examples or using the Query Analyzer you'll receive errors if you attempt to set a string value without single quotes surrounding the value as in the following example. Please keep this in mind during the following sections:

 1: UPDATE Products 2: SET UnitsInStock = '50' 3: WHERE ProductName = 'Alice Mutton' 

only for RuBoard