Updating data using SQLs

Step-by-step guide

In InfoWorks WS Pro, SQLs can be used to update network data of selected objects. In this exercise, you use SQL to first update the friction factor of pipes made of MDPE, and then a second time to populate a user text field.

1. From the Model Group, expand the SQL Model group.
2. Double-clicking the SQL Network to open the SQL Network and SQL Control on the GeoPlan.
3. Right-click Stored Query Group.
4. Select New > Stored Query.

5. In the popup, type the Name “MDPE FF”.
6. Click OK.
7. In the Model Group, double-click the MDPE FF stored query object.
8. In the Stored Query window, from the Object Type drop down, select Pipe.
9. In the query window, on the first line, type: SELECT FROM Pipe WHERE material = "MDPE";
10. On the second line, type: SET k = 0.1
11. Click the Test button.

A pop-up appears indicating valid syntax.

12. Click OK.
13. Click Save to save the SQL.
14. Click Run.

The SQL window closes, and the MDPE pipes are now selected in the GeoPlan.

15. On the Tools toolbar, click the Properties tool.

16. On the GeoPlan, select any of the highlighted pipes.

In the Properties window, notice the CW - k (mm) value changes to 0.1.

17. Commit the changes to the database.

To create the second stored query:

18. Right-click the Stored Query Group.
19. Select New > Stored Query.
20. In the popup, type the Name “User Text 1”.
21. Click OK.
22. In the Model Group, double-click User Text 1.
23. In the Stored Query window, in the Object Type drop-down, select Customer Point.
24. In the query window type: SET user_text_1 = "Significant Dom User" WHERE spec_consumption > 500
25. Click Test.
26. Click OK to close the notification.
27. Click Save.
28. Click Run.

29. Once the SQL has completed, in the Windows toolbar, expand the Grid windows drop-down.
30. Select New customer points window.
31. Scroll to the User Text 1 column.

Note that some customers are now specified as Significant Users.

32. Commit the changes to the database.