Find an Answer
UPDATE
Syntax
The UPDATE statement allows values or sub-trees in the PQL database to be modified.
pql_statement ::= UPDATE [@timestamp] path
SET|MERGE assignment_list
[WHERE boolean_expression]
timestamp ::= 'yyyy-mm-ddThh:mm:dd.uuuuuuZ'
assignment_list ::= assignment [, assignment]
assignment ::= path = expression | path = tree_literal
tree_literal ::= {tree_assignment [, tree_assignment]}
tree_assignment ::= identifier => expression | identifier => tree_literal
Description
| UPDATE | Modifies data in the PQL tree. If a timestamp is specified then the update is performed as if at that time in the past and so can be used to modify history for the tree. | |
| The path indicates the starting point in the tree to perform the modification. The actual nodes to be updates are determined by concatenating the path with each sub-path specified in the assignment clauses. | ||
| SET | MERGE | An update statement can either perform a SET operation where old data is replaced with new data, or a MERGE operation where GoToAssist makes an intelligent decision about which data to insert, which to update (replace), and which data to leave unchanged. | |
| WHERE | Limits which nodes are considered for update. |
Examples
You can try the following examples against fictitious data in the Query Sandbox:
- Example 1: Simple update
- Example 2: Specify the node
- Example 3: Set multiple values
- Example 4: Update historical values
- Example 5: Compare Update and Insert
Example 1: Simple update
Suppose you want to update your access point data. You could write a PQL statement like this:
UPDATE /network/device/interface[name='eth0']
SET name = 'eth17'
This PQL statement starts with the UPDATE keyword to replace existing data with new data. It is followed by the path, which specifies the nodes containing the data to replace. The SET name = 'eth17' clause indicates the new values.
If run against the sample database, this statement produces the following results:
row
*
network
device
system
name 5627
computed_vendor LANIER
computed_model 5627
computed_class printer
computed_score 115
os
version LANIER 5627 5.20
interface
name eth17
mac_address 01:01:02:03:04:05
in_octets 1995335536
out_octets 248342455
oper_status 1
device
system
name ESMITH
computed_vendor Intel
computed_class server
computed_score 10
os
version Linux
interface
name eth17
mac_address 02:A1:A2:A3:A4:A4
in_octets 1229592351
out_octets 1472928781
oper_status 1
interface
name eth1
mac_address 03:A1:A2:A3:A4:A5
in_octets 0
out_octets 0
oper_status 0
device
system
name AJONES
computed_vendor Intel
computed_class server
computed_score 10
os
version Linux
interface
name eth17
mac_address 04:A1:A2:A3:A4:A8
in_octets 1223098455
out_octets 1523093749
oper_status 1
interface
name eth1
mac_address 05:A1:A2:A3:A4:A9
in_octets 0
out_octets 0
oper_status 0
device
system
computed_model Ethernet Switch
computed_class switch
computed_score 25
os
version Ethernet Switch
interface
name eth17
mac_address 06:13:72:F3:0A:F5
in_octets 0
out_octets 0
oper_status 2
interface
name eth1
mac_address 07:13:72:F3:0A:F6
in_octets 0
out_octets 0
oper_status 2
device
system
name KRYPTON
computed_model Windows Workstation
computed_class workstation
computed_score 125
os
version Darwin Kernel Version 9.2.2
interface
name eth17
mac_address 08:16:CB:FF:FE:66
in_octets 0
out_octets 346
oper_status 1
interface
name eth1
mac_address 09:16:CB:FF:FE:67
in_octets 0
out_octets 0
oper_status 2
interface
name eth2
mac_address 10:16:CB:FF:FE:68
in_octets 0
out_octets 0
oper_status 2
interface
name eth3
mac_address 11:16:CB:FF:FE:69
in_octets 1598233842
out_octets 2360815490
oper_status 1
device
system
name NISSINGETTY
computed_vendor Juniper Networks
computed_model Netscreen Router
computed_class router
computed_score 60
os
version NetScreen-5GT 5.1.043a
interface
name eth17
mac_address 12:10:D8:99:2B:C2
in_octets 1385354750
out_octets 1451321493
oper_status 1
interface
name eth1
mac_address 13:10:D8:99:2B:C1
in_octets 3312952833
out_octets 3239791359
oper_status 1
Example 2: Specify the node to update
You must specify the exact node for update. You can do so in two ways:
Example 2a: Specify nodes through qualified paths
This statement specifies the node through a qualified path:
UPDATE /network/device SET interface[mac_address='01:01:02:03:04:05']/oper_status = '3' WHERE name = 'eth0'
If run against the sample database, this statement produces the following results:
row
*
network
device
system
name 5627
computed_vendor LANIER
computed_model 5627
computed_class printer
computed_score 115
os
version LANIER 5627 5.20
interface
name eth0
mac_address 01:01:02:03:04:05
in_octets 1995335536
out_octets 248342455
oper_status 3
device
system
name ESMITH
. . .
Example 2b: Specify nodes through predicates
This example specifies the node through the predicate:
UPDATE /network/device/interface[mac_address = '01:01:02:03:04:05'] SET mac_address='A1:A1:A2:A3:A4:A5'
If run against the sample database, this statement produces the following results:
row
*
network
device
system
name 5627
computed_vendor LANIER
computed_model 5627
computed_class printer
computed_score 115
os
version LANIER 5627 5.20
interface
name eth0
mac_address A1:A1:A2:A3:A4:A5
in_octets 1995335536
out_octets 248342455
oper_status 1
device
system
name ESMITH
. . .
Example 3: Set multiple values
This example specifies the node through a predicate, while setting multiple values in the same statement.
UPDATE /network/device/interface[mac_address = '01:01:02:03:04:05'] SET out_octets='3000000000', in_octets='2000000000'
If this statement is run on the sample database, it produces the following results:
row
*
network
device
system
name 5627
computed_vendor LANIER
computed_model 5627
computed_class printer
computed_score 115
os
version LANIER 5627 5.20
interface
name eth0
mac_address 01:01:02:03:04:05
in_octets 2000000000
out_octets 3000000000
oper_status 1
device
system
nameESMITH
. . .
Example 4: Update historical values
This example updates historical values by adding a timestamp. Note that the timestamp must be in single quotes: '2008-04-05T19:06:05.192700Z'.
UPDATE@'2008-04-05T19:06:05.192700Z' /network/device SET traffic_in = 700, traffic_out = 800 WHERE interface/mac_address = '01:01:02:03:04:05'
This statement produces the same results:
row:
row
*
network
device
system
name 5627
computed_vendor LANIER
computed_model 5627
computed_class printer
computed_score 115
os
version LANIER 5627 5.20
interface
name eth0
mac_address 01:01:02:03:04:05
in_octets 700
out_octets 800
oper_status 1
device
system
name ESMITH
. . .
After you update the database, you can then run a query to see the resulting history:
SELECT history( traffic_in, traffic_out, interface/ip_address ) FROM /network/device
If this statement is run on the sample database, it produces the following results:
row
history(traffic_in, traffic_out, interface/ip_address)
history
when 2008-04-05T19:06:05.192700Z
history
when2008-04-06T02:18:05.192700Z
history
when2008-04-06T09:30:05.192700Z
history
when2008-04-06T16:42:05.192700Z
history
when2008-04-06T23:54:05.192700Z
history
when2008-04-07T07:06:05.192700Z
history
when2008-04-07T14:18:05.192700Z
. . .
Example 5: Compare UPDATE and INSERT
UPDATE statements replace values; INSERT adds them. In the following example the INSERT statement adds a new PRINTER, whereas the UPDATE statement changes the name of the 5627 printer to PRINTER:
INSERT INTO / VALUES {
device => {
name => 'PRINTER',
interface => {
mac_address => 'B1:B2:B3:B4:B5:B6'
}
}
}
UPDATE /network/device/system[name = '5627'] SET name = 'PRINTER'
If run against the sample database, the INSERT statement produces very different results from the UPDATE statement:
row
*
network
device
name PRINTER
interface
mac_address B1:B2:B3:B4:B5:B6
device
system
name PRINTER
computed_vendor LANIER
computed_model 5627
computed_class printer
computed_score 115
os
version LANIER 5627 5.20
interface
name eth0
mac_address A1:A1:A2:A3:A4:A5
in_octets 1995335536
out_octets 248342455
oper_status 1
device
system
name ESMITH
. . .
For more examples, see Examples and More examples.
Related topics
| Views: 1361 | Last Updated: Thu, May 09 2013 2:28 PM
About Us | Research Participation | Terms of Service | Privacy Policy | Join us on: Twitter | Facebook
© 2013 Citrix Online, LLC. All rights reserved. Citrix, GoToAssist, GoToMeeting, GoToMyPC, GoToTraining, GoToWebinar, Podio and Sharefile are trademarks of Citrix Systems, Inc., or a subsidiary thereof, and are or may be registered in the U.S. Patent and Trademark Office and other countries. All other trademarks are the property of their respective owners.