Update query

Update queries make bulk changes to data based on a set of criteria. (Unlike Select queries that just show the data.)

This article explains how to write a update query, e.g. in CFE.

The steps below use one example to demonstrate how to replace a specific item in a data field with another.

Example for demonstration of steps below:

• in the "R_dc_treat" field in "L_log", there are erroneous values of "-1" however, all of these values must be replace with the value "DC".

Steps broken down into 4 parts

Part 1

Set up a Selection Query. See Picture: Part 1 setting up update query

• this allows you to look if the update you're are planning is going to do what you need it to do before you actually proceed to part 2.
• Open CFE
• in the tool bar select the following:
  • CREATE
  • Select: Query Design. A list of tables will appear
  • Select: L_Log
  • Close the table box
  • Scroll down and find R_dc_treat.
  • Double click on R_dc_treat TWICE
    • You should see R_dc_treat in two columns in the FIELD box.
  • in the first column in the CRITERIA field, enter: "-1"
  • In the SECOND column, change to field name to the following: NewValue:"DC"
  • Select DATASHEET VIEW. You will see in your first column what the value currently is and in the second column, what the value will be replaced with.
  • SAVE with a intuitive name of what this update query will do.
  • Go back to DESIGN VIEW. Make any changes if needed.

Part 2

Change this from a Selection Query to and Update Query that will actually prepare you to make change you set up after you run it. See Picture Setting up Update Query Part 2-4

• Go to TAB at top of screen: Query Tools - Design
• Select and SAVE as : Query Type - APPEND
  • • notice that the symbol beside the query you named will be different.
    • notice that the row that was labelled sort has now changed to: Update to.

Part 3

• in the first column in the Update to field, type in: "DC"
• in the second column, first row called Field, remove the entry from the whole field: NewValue:"DC"
• SAVE again as a APPEND - query type.

Part 4-Ready to change data

Press the red ! run button.

• query has done it's job.