Performing a bulk update
The bulk update feature of the Participants activity makes it easy to update many participant records at once. You specify the records that you want to update and the updates that you want to make by typing two expressions similar to those used in SQL statements. Alternatively, you can just display a selection of records without updating them. To use the bulk update feature, select the Advanced option under “Select and Edit Options” on the View Settings tab (
Viewing and editing participant records).
Notes
▪Always use the bulk update feature with care, as Participants does not have an undo option.
▪You cannot delete records by using the bulk update feature; instead, see
Deleting records).
▪To change an protected field, you must first unlock the field.
▪You cannot change protected fields by using the Update option when Advanced editing is selected. The UPDATE statement is executed as is without applying encryption.
Selecting records
To specify the records that you want to select for updating or displaying (that is, your selection criteria), type an expression of the following form:
column_name comparison_operator value
An expression can also be of the following form:
column_name IS [NOT] NULL
Your selection criteria can consist of two or more expressions (or “sub-expressions”), combined by using the AND, OR, and NOT logical operators.
Parameters
column_name
Specifies the name of the column on the sample table. You can specify only a column that you have access to. For more information, see
Controlling access to data and features.
comparison_operator
Must be one of the following operators: = (equals), < (less than), > (greater than), <= (less than or equal to), >= (greater than or equal to), !< (not less than), !> (not greater than), <> (not equal to), or != (not equal to).
You can also specify the Transact-SQL LIKE operator, which will allow you to include wildcard characters in your value. Some examples of wildcard characters are:
%, which means any string of zero or more characters.
_ (an underscore), which means any single character.
[abc], which means any of the characters a, b, or c.
[w-z], which means any characters in the range w to z.
[^de], which means any character except d or e.
value
Specifies a value with the same data type as the column. If the column is a text or datetime column, enclose the value in single quotation marks. If a text value contains double-byte characters, add the capital letter N before the opening single quotation mark to specify that the value is Unicode. If the column is a datetime column, the value must be in one of three formats: yyyy-mm-dd, yyyy-mm-dd:hh:mi, or yyyy-mm-dd hh:mi:ss. If you specify only the date, a time of 00:00:00 is assumed.
Notes
Depending on the sample database that you are using, text searches might be case sensitive. If so, you must type text values so that they match the case of the data in the sample table. For example, queue names such as COMPLETED must always be typed in upper case.
Examples of selection criteria
To select all records that are in the COMPLETED queue:
Queue = 'COMPLETED'
To select all records that are in the COMPLETED queue and have an Id between 100 and 200:
Queue = 'COMPLETED' AND Id >= 100 AND Id <= 200
To select all records that are in either the FRESH or ACTIVE queue:
Queue = 'FRESH' OR Queue = 'ACTIVE'
To select all records whose Name column includes the Unicode text “Smith”:
Name LIKE N'Smith'
To select all records that have an appointment on 23 February 2006:
AppointmentTime >= '2006-02-23' AND AppointmentTime < '2006-02-24'
To select all records whose Test column is not null:
Test IS NOT NULL
Displaying records without updating them
1 In the Selection Criteria box, type your expression. To select all records, make sure that the Selection Criteria box is empty.
2 Make sure that the Update box is empty.
3 Click Apply.
The list of participant records will now include only those records that match your selection criteria. If there are more records than will fit on one page, use the icons at the bottom of the page to navigate between pages.
Defining bulk updates
You define the update that you want to perform on the selected records by typing an expression of the following form:
column_name = new_value
column_name
Specifies the name of the column on the sample table. Type the column name so that its case matches the case of the field name as displayed in Participants. This rule applies even when the sample database has been set up to be case-
insensitive. In addition, you can specify only a column to which you have access: for more information, see
Controlling access to data and features.
new_value
Specifies the value that will be assigned to the column. For more information, see the description of
value in
Selecting records.
You can update several columns at once by separating the expressions with commas.
Performing a bulk update
1 In the Selection Criteria box, type an expression that specifies the records that you want to update. For more information, see
Selecting records. To select all records, make sure that the Selection Criteria box is empty.
2 In the Update box, type an expression that specifies the update that you want to perform. For example, to assign all selected records to the APPOINTMENT queue and change the value of the PreviousQueue column to SILENT, type the following:
Queue = 'APPOINTMENT', PreviousQueue = 'SILENT'
3 Click Apply. The Confirm Update dialog opens.
4 Click OK.
The list of participant records will now include only those records that match your selection criteria. If the columns that you updated are visible, the new values are displayed.
See also