Updating Data Using SQL
InfoWorks ICM includes the SQL keywords SET and UPDATE that allow network data to be modified using an SQL expression.
- The SET keyword can be used on its own or in conjunction with a WHEREsub-clause to update the current object type chosen in the Object type field of the SQL Dialog. See Updating the Current Object Type below.
- The UPDATE keyword is used in conjunction with the SET and WHERE keywords to update objects of one or more types; not necessarily the type selected in the Object type list on the SQL Dialog. See Updating a Different Object Type below.
See SQL Syntax for more information on keywords.
See the SQL Examples section for further examples of the use of SQL in InfoWorks ICM.
Updating the Current Object Type
The SET keyword can be used on its own or in conjunction with a WHEREsub-clause to update the current object type chosen in the Object type field of the SQL Dialog.
The syntax is as follows:
Using the SET Keyword
SET <assignment>
e.g.
SET user_text_1 = "text"
Sets user_text_1 field to text for all objects of the chosen object type (selected in the Object Type field of the SQL dialog).
- Separate multiple assignments in the same statement by commas e.g.
SET user_text_1 = "text", user_number_1 = x
Sets user_text_1 field to text and user_number_1 field to the value in the x field for all objects of the chosen object type.
Using SET and WHERE
SET <assignment> WHERE <conditional expression>
e.g.
SET user_number_1 = 1 WHERE x > 100000
Sets user_number_1 field to 1 for all objects of the chosen object type with x coordinate > 100000
- Separate multiple assignments in the same statement by commas e.g.
SET user_number_1 = 1, user_number_2 = 2 WHERE x > 100000
- Separate multiple statements by semicolons e.g.
SET user_number_1 = 1, user_number_2 = 2 WHERE x > 100000; SET user_number_3 = 3;
Sets user_number_1 to 1 and user_number_2 to 2 for all objects of the chosen object type with x coordinate > 100000 and sets user _number 3 to 3 for all objects of the chosen object type.
Updating Values Within an Array
To update values in an array within an object:
SET <array name.array fieldname> = <expression>
See SQL and Array Data for more information on the use of SQL Queries with arrays.
Examples
Click on the images to reveal the examples.
Example 1
Example 2
Updating a Different Object Type
The UPDATE keyword is used in conjunction with the SET and WHERE keywords to update objects of one or more types; not necessarily the type selected in the Object type list on the SQL Dialog.
Separate multiple assignments in the same statement by commas and separate multiple statements by semicolon as described in the Updating the Current Object Type section above.
Click on the images to reveal the examples.
Example 1
Example 2
