Issue
Update (Transact-SQL) is used to modify data in a table. A special case were data is transferred from one source to a different source will be discussed.
Product Line
Struxureware Power Monitoring 7.0.1, Power Monitoring Expert 2023, SQL Server (any version).
For ION Enterprise 6.0 SP1 and earlier versions, refer to article FA207789.
Environment
SQL Server Management Studio (SSMS).
DataLog2 table (the table in which data is stored in).
Cause
Data was originally stored under a source and later due to some changes, stored under a different source.
Resolution
step1: The best strategy is to run a select statement to view the data that will be modified in a later step. This will eliminate the possibility of modifying data mistakenly. It is important to narrow the select statement to specific quantityid and probably timestamp.
select *
from datalog2
where sourceid=2 and QuantityID in (11366, 11367)
order by TimestampUTC asc
step2: Modify the select statement to update and specify the column that will be changed. In our test it is the data in sourceid column that needs to be modified:
update datalog2
set [sourceid] = 293
from datalog2
where sourceid=2 and QuantityID in (11366, 11367)
In case it is required to narrow the select statement further to specific time range, the two statements above will take the form:
select *
from datalog2
where sourceid=2 and QuantityID in (11366, 11367) and (TimestampUTC > CONVERT(DATETIME, '2013-03-20', 102)) and (TimestampUTC < CONVERT(DATETIME, '2013-04-28', 102))
order by TimestampUTC asc
update datalog2
set [sourceid] = 293
from datalog2
where sourceid=2 and QuantityID in (11366, 11367) and (TimestampUTC > CONVERT(DATETIME, '2013-03-20', 102)) and (TimestampUTC < CONVERT(DATETIME, '2013-04-28', 102))
Update (Transact-SQL) is used to modify data in a table. A special case were data is transferred from one source to a different source will be discussed.
Product Line
Struxureware Power Monitoring 7.0.1, Power Monitoring Expert 2023, SQL Server (any version).
For ION Enterprise 6.0 SP1 and earlier versions, refer to article FA207789.
Environment
SQL Server Management Studio (SSMS).
DataLog2 table (the table in which data is stored in).
Cause
Data was originally stored under a source and later due to some changes, stored under a different source.
Resolution
**Note: Irreparable database damage can occur. Only users familiar with SQL Server Management Studio should perform these tasks. Ensure databases are backed up prior to performing these tasks.**
In this example data (quantityid=11366 and 11367) was originally logged under sourceid=2. Later data was logged under sourceid=293. As a result data became discontinuous.step1: The best strategy is to run a select statement to view the data that will be modified in a later step. This will eliminate the possibility of modifying data mistakenly. It is important to narrow the select statement to specific quantityid and probably timestamp.
select *
from datalog2
where sourceid=2 and QuantityID in (11366, 11367)
order by TimestampUTC asc
step2: Modify the select statement to update and specify the column that will be changed. In our test it is the data in sourceid column that needs to be modified:
update datalog2
set [sourceid] = 293
from datalog2
where sourceid=2 and QuantityID in (11366, 11367)
In case it is required to narrow the select statement further to specific time range, the two statements above will take the form:
select *
from datalog2
where sourceid=2 and QuantityID in (11366, 11367) and (TimestampUTC > CONVERT(DATETIME, '2013-03-20', 102)) and (TimestampUTC < CONVERT(DATETIME, '2013-04-28', 102))
order by TimestampUTC asc
update datalog2
set [sourceid] = 293
from datalog2
where sourceid=2 and QuantityID in (11366, 11367) and (TimestampUTC > CONVERT(DATETIME, '2013-03-20', 102)) and (TimestampUTC < CONVERT(DATETIME, '2013-04-28', 102))
發佈於: 施耐德電機Taiwan
