SQL for updating data
InfoWorks WS Pro includes the SQL keywords SET and UPDATE that allow network data to be modified using a SQL expression.
- The SET keyword can be used on its own or in conjunction with a WHERE sub-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 topics for further examples of the use of SQL in InfoWorks WS Pro.
Updating the current object type
The SET keyword can be used on its own or in conjunction with a WHERE sub-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>
For example,
SET user_text_1 = "text"
Show image
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; for example,
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>
For example,
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 greater than 100000.
- Separate multiple assignments in the same statement by commas; for example,
SET user_number_1 = 1, user_number_2 = 2 WHERE x > 100000
- Separate multiple statements by semicolons; for example,
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 greater than 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
Some further examples are presented below.
Example 1
SET diameter = 300 WHERE diameter_flag='CH' ; SET length=10 WHERE length < 10
The first part sets a diameter of 300 for all pipes where the data flag on pipe diameter is set to "CH". The second part sets a minimum length of 10m for all pipes less than 10m in length.
Example 2
This will set the User Text 5 field of any nodes inside a GIS polygon in the GIS Layer "Region" to the value in the "Category" field of the GIS polygon.
See Including GIS data in SQL queries 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 Updating the current object type above.
Example 1
UPDATE <object type> SET <assignment>
For example,
UPDATE [All Links] SET user_text_1 = "text"
sets user_text_1 field to text for all link objects.
Example 2
UPDATE <object type> SET <assignment> WHERE <conditional expression>
For example,
UPDATE node SET user_number_1 = 1 WHERE x > 100000
sets user_number_1 field to 1 for node objects with x coordinate greater than 100000.
