Solved

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

Posted on 2014-12-01
4
92 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
  • 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now