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 the Updating the current object type section 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 the Updating a different object type section 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>

Example 1

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).

Example 2

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>

Example 1

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.

Example 2

Separate multiple assignments in the same statement by commas, e.g.

SET 
  user_number_1 = 1, 
  user_number_2 = 2 
 WHERE x > 100000

sets fields 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.

Example 3

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 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>

Note: See SQL and array data for more information on the use of SQL queries with arrays.

Example 1

SQL query context:

• Object type: Pipe

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

SQL query context:

• Object type: Node
• [Spatial] Search type: Cross
• [Spatial] Layer type: GIS layer
• [Spatial] Layer: [SHP] Roads

SET 
  user_text_5 = gislayer.AREA

This will set the user_text_5 field of any nodes inside a GIS polygon in the GIS Layer "Roads" to the value in the "AREA" field of the GIS polygon.

Important: This example utilises the Spatial Search functionality within SQL, specifically the implicit search functionality when defining spatial search options within the SQL dialog. See SQL and spatial searches for more information on including background Network and 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.

UPDATE <object type> SET 
  <assignment>

UPDATE <object type> SET 
  <assignment> 
 WHERE <conditional expression>

Separate multiple assignments in the same statement by commas and separate multiple statements by semicolon, as described in Updating the current object type section above.

Example 1

UPDATE [All Links] SET 
  user_text_1 = "text"

sets user_text_1 field to text for all link objects.

Example 2

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.