UPDATE Statement (SQL Only)

See AlsovbhowUPDATESee                 ExamplevbhowUPDATEEx>Low

Description

You can use UPDATE in an action queryKV0EYL to create an update query that changes records in the tables listed in the FROM clause.  You can use expressions to make the change.  For example, you can reduce the price of all beverages by 10 percent or increase mailing charges for all clothing items by 3 percent.

Notes

         UPDATE is especially useful when you want to change many records or when the records are in multiple tables.

         Use the SET reserved word to specify the new values.  In the following example, the new value for the Freight field is set to the existing value plus 3 percent.

  UPDATE Orders
  SET Freight = Freight * 1.03
  WHERE [Ship Country] = 'UK'

 

         You can change several fields at the same time.  The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent:

  UPDATE Orders
  SET [Order Amount] = [Order Amount] * 1.1, Freight = Freight * 1.03
  WHERE [Ship Country] = 'UK'

 

         No records are returned when you use the UPDATE statement.

         In some cases, you can edit the data in a query's Dynaset to change the data in the underlying tables;  in other cases, you cannot.

 

Warning   If an SQL statement changes more rows than the internal transaction log can hold, Visual Basic will force the transation to commit the transaction in stages. If your program is interrupted before the operation is completed, only the last, uncompleted stage(s) will be rolled back. In this case only some of the records to be changed will have been affected. If you use a BeginTrans statement before the SQL statement, a trappable error will occur if the transaction log fills prior to the completion of your statement, and all of the changes made by your statement will be automatically rolled back.