Mac
asked on
SQL 2008 R2 Updating multiple values in a table while copying old value to comment field
I need to automate this process. I have to update fields when the feeding instrument goes awry. I use the following to list the data in question.
Which gives a result like this:
then I use the following for each entry I need to update.
As you can see this is a one-at-a-time thing, three of which I have done . Today I have to do this to 120 records so its time to see how to run through this in one shot.
I would like the code to look at a subset of data as was returned by the first query, then run through that data replacing the original values with the constant while updating the comment field with the original value so it isn't lost forever.
AS you can see I have my toe dipped into SQL but cant put all the pieces together, I would appreciate some guidance or sample code.
Declare @StDate DateTime
Declare @EndDate DateTime
Set @StDate = '11/15/2014 00:00:00.000' ------ SET BEGIN DATE
--Set @EndDate = DateAdd(month,1,@StDate) -- One Month
SET @EndDate = DateAdd(day,1,@StDate) --One Day
Select dateadd(day,(datediff(day,0,TIMEandDATE)),0) as ReadingDate, timeanddate,Value, id, tagid, _COMMENT
From MtTable
where
TagID = 3004 and
value > 0.07 and
TimeAndDate >= @StDate and
TimeAndDate < @EndDate
order by timeanddate
Which gives a result like this:
ReadingDate timeanddate Value id tagid _COMMENT
2014-11-15 00:00:00.000 2014-11-15 01:10:00.800 0.07247794 6644394 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 01:15:00.790 0.1055236 6644421 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 01:20:00.860 0.1270975 6644448 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 01:25:00.850 0.1387833 6644475 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 01:30:00.943 0.1474728 6644502 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 01:35:00.930 0.1567615 6644529 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 01:40:00.917 0.18 6644556 3004 Orig Value 0.5777515
2014-11-15 00:00:00.000 2014-11-15 01:45:00.037 0.2044038 6644583 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 01:50:00.017 0.1886086 6644610 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 01:55:00.007 0.1853127 6644637 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 02:00:00.107 0.1844994 6644664 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 02:05:00.087 0.1898072 6644691 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 02:10:00.163 0.189 6644718 3004 Orig Value 1.657301
2014-11-15 00:00:00.000 2014-11-15 02:15:00.120 0.229 6644745 3004 Orig Value 1.157635
2014-11-15 00:00:00.000 2014-11-15 02:20:00.103 0.2700243 6644772 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 02:25:00.197 0.2093264 6644799 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 02:30:00.180 0.2127936 6644826 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 02:35:00.170 0.2254212 6644853 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 02:40:00.263 0.1982827 6644880 3004 NULL
2014-11-15 00:00:00.000 2014-11-15 02:45:00.257 0.1922043 6644907 3004 NULL
then I use the following for each entry I need to update.
Update MtTable
Set Value = 0.18 , _COMMENT = 'Orig Value 0.5777515' ----- SET CORRECT VALUES
where id = 6644556 ---- ENTER SELECTED ID HERE
As you can see this is a one-at-a-time thing, three of which I have done . Today I have to do this to 120 records so its time to see how to run through this in one shot.
I would like the code to look at a subset of data as was returned by the first query, then run through that data replacing the original values with the constant while updating the comment field with the original value so it isn't lost forever.
AS you can see I have my toe dipped into SQL but cant put all the pieces together, I would appreciate some guidance or sample code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Shaun,
I think you got it.
I assume here that the ISNULL function checks for a null first ? I looked it up and there are similar functions; ISNULL, IS NULL and by the examples given in the help, I can't yet grasp the difference between them. Just want to be sure what to expect from this.
This is live database so I need to make sure I get this right the first time. Its not mission critical, but I'll never hear the end of it if I do something weird to it.
I think you got it.
I assume here that the ISNULL function checks for a null first ? I looked it up and there are similar functions; ISNULL, IS NULL and by the examples given in the help, I can't yet grasp the difference between them. Just want to be sure what to expect from this.
This is live database so I need to make sure I get this right the first time. Its not mission critical, but I'll never hear the end of it if I do something weird to it.
ASKER
Thank you for the quick reply. It worked perfectly and I learned something new. Love that !
Set Value = 0.18 , _COMMENT = 'Orig Value' + Value ----- SET CORRECT VALUES
where id = 6644556 ---- ENTER SELECTED ID HERE