Solved

SQL 2008 R2 Updating multiple values in a table while copying old value to comment field

Posted on 2014-12-01
4
115 Views
Last Modified: 2014-12-01
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.
0
Comment
Question by:Salad-Dodger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Expert Comment

by:jimtpowers
ID: 40474387
Update MtTable
Set Value = 0.18 , _COMMENT = 'Orig Value' + Value -----   SET CORRECT VALUES
where id = 6644556 ----  ENTER SELECTED ID HERE
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 40474392
This update should do the trick:
UPDATE MTTable
SET Value = 0.18,
    _Comment = LTRIM(ISNULL(_Comment, '') + ' Orig Value ' + CAST(Value AS VARCHAR(20)))
WHERE TagID = 3004  and
    Value > 0.07 and
    TimeAndDate >= @StDate and
    TimeAndDate < @EndDate

Open in new window

0
 
LVL 1

Author Comment

by:Salad-Dodger
ID: 40474512
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.
0
 
LVL 1

Author Closing Comment

by:Salad-Dodger
ID: 40474572
Thank you for the quick reply. It worked perfectly and I learned something new.  Love that !
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question