Updating Existing Records

Updating Existing Records

As with adding records, updating existing records can be done both manually through the GUI and programmatically with SQL. The manual method simply requires navigating to the correct record (sorting the table can often help with this step) and replacing the old value(s) with new one(s).

SQL UPDATE statements are used in a couple of different scenarios. The first is when you have bulk edits to make to a table. For example, let's say you wanted to add a region field to a states table. You might add the field through the table design GUI, then execute a series of UPDATE queries to populate the new region field. The logic of the queries would take the form, "Update the region field to X for the states in this list Y." We'll perform a bulk update like this in a moment.

The other common usage for the UPDATE statement goes back to the website account scenario. Let's say, you've moved and need to provide the company with your new address. Upon clicking the Submit button, a script will generate and execute an UPDATE query on your record in the table.

A. Performing an UPDATE query

After working with your album database for a while, you notice that you're constantly using string concatenation to add the word "Records" to the names of the companies in the Labels table. You decide that the benefit of adding that text to the label names outweighs the drawback of storing that text redundantly.

1. Select Create > Query Design.
2. Add the Labels table to the query design GUI, and close the Show Tables dialog.
3. In the Query Type section of the Design ribbon, change the query type from Select to Update. Note that the Show and Sort rows in the design grid disappear (this query type doesn't show any results) and an Update To row takes their place.
4. Double-click on the Label_Name field to add it to the design grid.
5. In the Update To cell, enter the following expression (be sure to include the space before the word Records):
    

   [Labels]![Label_Name] & " Records"

   If you needed to, you could specify selection criteria to limit the update to a subset of records. For example, following on the state-region example mentioned above, you might update the region field to "New England" using a WHERE condition of:
   WHERE state IN ('CT','MA','ME','NH','RI','VT').
   In this case, we want to perform this update on all records, so our query is done.
6. Execute the query and open, or refresh, the Labels table to confirm that it worked.
7. Return to the query and select View > SQL View to see the SQL statement that you created through the GUI:
    

   UPDATE Labels SET Labels.Label_Name = [Labels]![Label_Name] & " Records";

   As discussed earlier when we were dealing with SELECT queries, the brackets and exclamation point do not follow the SQL standard. You could also omit the parent table in the field specification if there is no chance of duplicate field names in your query. Thus, the following syntax would also work and be closer to standard SQL:
    

   UPDATE Labels SET Label_Name = Label_Name & " Records";

   Also, as mentioned before, concatenation requires a different syntax depending on the RDBMS.
8. Close the query without saving.