Link to home
Start Free TrialLog in
Avatar of Mac
MacFlag for United States of America

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.
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

Open in new window


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

Open in new window


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

Open in new window


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.
Avatar of jimtpowers
jimtpowers
Flag of United States of America image

Update MtTable
Set Value = 0.18 , _COMMENT = 'Orig Value' + Value -----   SET CORRECT VALUES
where id = 6644556 ----  ENTER SELECTED ID HERE
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mac

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.
Avatar of Mac

ASKER

Thank you for the quick reply. It worked perfectly and I learned something new.  Love that !