The Perfion Query – UPDATE
- Morten Lindequist Køhler
- Thomas Olthuis
Using the Update-statement it is possible to update feature values on items in Perfion. Update works on both selectables and non-selectable features
The below example will set the English value for the selectable feature Color to “Black” for all items that has the feature ItemName set to “1234”.
XML:
<Query>
<Update>
<Item>
<Color language='EN'>Black</Color>
</Item>
</Update>
<From id="Product" />
<Where>
<Clause id="ItemName" operator="=" value="1234" />
</Where>
</Query>JSON:
{
"query": {
"Update": {
"Item": {
"Values": [
{ "name": "Color", "language": "EN", "value": "Black" },
]
}
},
"From": { "id": "Product" },
"Where": {
"Clauses": [
{ "Clause": { "id": "ItemName", "operator": "=", "value": "1234" } }
]
}
}
}Since Color is a selectable and “Black” hereby represents an item the same can be achieved by the following update:
XML:
<Query>
<Update>
<Item>
<Color id='347' />
</Item>
</Update>
…
</Query>JSON:
As can be seen it is here the id if the item that is specified instead of the value of it. The result is the same.
The Language-attribute may only be specified for localizable features, and only for selectables if you update them by value rather than by id.
Specifying a value for a selectable only works if the value uniquely defines a single item. If two or more items are named the same, an error will be returned, and nothing will be updated. The same will happen if no item has the specified value.
The base value of an item is updating by using the name value as shown below:
XML:
JSON:
Updating Parent Item and Brand
Updating Parent Item and Brand is simply adding the attributes “Brand” and/or ParentID to the <Item>-Element.
XML:
JSON:
Updating multiple features
Updating multiple features simultaneously is simply achieved by specifying multiple features inside the <Item>-Element.
XML:
JSON:
Updating multi-value features
Updating multi-selectable features is achieved by specifying the same feature multiple times. The example below shows exactly this for a selectable multi value. Note that it is allowed to combine assigning values by their id with assigning the values themselves.
XML:
JSON:
Clearing values
Clearing a value is done by setting the feature to nothing instead of specifying a value or Item ID.
XML:
JSON:
Support for time zone
When updating items based you can choose to update items based on some criteria where you use some timestamp-information. In such cases it might be useful to be able to supply information about the time zone. This has been possible since version 4.7 as the following example demonstrates:
XML:
JSON:
In the above example all Products modified between 11:00:00 and 12:00:00 (both included) on January 17th, 2020 Eastern Standard Time will have the feature “Color” set to “Blue”. If no time zone is supplied, the above query will use whatever time zone in which the database is running. Before Perfion 4.7 the database time zone where always used.