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
SET base_flow = base_flow – 0.002, base_flow_flag = 'FLAG1' WHERE base_flow <0.022 AND base_flow >0.016; SET base_flow = base_flow –0.004, base_flow_flag = 'FLAG2' WHERE base_flow >=0.022
This decreases the Base Flow for a subcatchment by 0.002 for values between 0.016 and 0.022, and by 0.004 for values greater than or equal to 0.022. In both cases the flag for the field is set to indicate the change made (assuming FLAG1 and FLAG2 have already been defined as flags).
Example 2 Example
This will set the User Text 5 field of any nodes inside a GIS polygon in the GIS Layer "MMap1_region" to the value in the "Area" field of the GIS polygon.
See SQL and GIS Data for more information on including GIS Layers in SQL Queries.
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
UPDATE <object type> SET <assignment>
e.g.
UPDATE [All Links] SET user_text_1 = "text"
Sets user_text_1 field to text for all link objects.
Example 2 Example
UPDATE <object type> SET <assignment> WHERE <conditional expression>
e.g.
UPDATE node SET user_number_1 = 1 WHERE x > 100000
Sets user_number_1 field to 1 for node objects with x coordinate > 100000.