Update syntax

The AVEVA Historian implements UPDATE only by the OPENQUERY function, not by a four-part syntax. The reason for this is the method of implementation of UPDATE in Microsoft SQL Server. If you attempt to update values using the four-part query syntax, you will receive an error.

Also, a limitation of using the OPENQUERY function is that SQL variables cannot be used in the UPDATE statement.

Updating data in history always results in a new history version, and can be performed multiple times; however, only the original or the latest version of the data is available upon retrieval.

Syntax

The syntax of the UPDATE statement in the OPENQUERY portion is:

SELECT * FROM OpenQuery(INSQL, 'UPDATE { table_name }

SET

column_name = constant [,...n]

WHERE

<search_condition>')

Arguments

table_name

The name of the extension table into which you want to update the data. Valid values are: AnalogHistory, DiscreteHistory, StringHistory or History.

column_name

Valid values are: Value, OPCQuality. (Update of the vValue column of the History table is not supported.)

Remarks

For the <search_condition>, DateTime and TagName search criteria are mandatory. The DateTime criterion must refer to a time range; an update at a single time point ('DateTime=') is not supported.

Important: When updating data using the OLE DB provider, the greater than operator (>) and the less than operator (<) are always interpreted as >= and <=, respectively. For more information, see Example 2 in this section.

DateTime >[=] earlier_datetime_value AND DateTime <[=] later_datetime_value

Similarly, TagName may refer to one or more tags:

TagName = ...

-or-

TagName [NOT] LIKE ...

-or-

TagName IN ( ... )

'TagName NOT IN (…)' is not supported. This is similar to the capabilities of OpenQuery SELECT; 'NOT IN' syntax is also not supported here.

As with INSERT … VALUES, wwTimeZone is optional. If not specified, the time zone defaults to the time zone of the AVEVA Historian.

Important: Other types of search conditions (for example, using a condition on Value) are not supported.

Example 1

The supported UPDATE syntax is shown in the following example:

SELECT * FROM OPENQUERY(INSQL, 'UPDATE History SET Value = 10, OPCQuality = 192

WHERE TagName LIKE "Line1V%"

AND DateTime >= "1999-11-11 16:05:10"

AND DateTime <= "1999-11-11 16:05:40" ')

This query sets the Value to 10 and the OPCQuality to 192 for all data values for the specified tags, when the specified DateTime criteria are met.

Example 2

For the following query, the data that is updated will include the timestamps of 2002-10-03 14:59:59 and 2002-10-03 16:00:00, respectively. Existing points at these timestamps will therefore be affected by the update.

SELECT * FROM OPENQUERY(INSQL, 'UPDATE History SET Value = 1, OPCQuality = 192

WHERE TagName = "Manual_AD32SI1"

AND DateTime > "2002-10-03 14:59:59"

AND DateTime < "2002-10-03 16:00:00" ')