Query Help Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help Question

Author

Topic

srheal
Starting Member

2 Posts

Posted - 2007-08-22 : 08:03:59

Hello, I am trying to use a subquery to return a current value from a table and add it to a specified value then update the data. But it is only returning the current value and not adding I have the proper operator but it isnt working. Everything in blue is what I am trying to get to work. The blue code should get the current value and add it to the parameter value.
UPDATE errCHDHold
--This processes but its only returning the current value
--It should be returning the current value plus the 86400
SET wrapTime = @Wrap_Time + (select wrapTime from errCHDHold where errorDesc in ('Invalid Wait Time')), endTime = @End_Time + (select endTime from errCHDHold where errorDesc in ('Invalid Wait Time')),
talkTime = @Talk_Time + (select talkTime from errCHDHold where errorDesc in ('Invalid Wait Time')) WHERE startdate = @Current_Date AND source Not IN ('ADMIN','TRAINING','STAFF') AND
errorDesc LIKE 'Invalid Wait%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 08:09:42

What is the objective? I don't think your query will ever work, logically.

UPDATE	x
SET	x.wrapTime = @Wrap_Time + (select TOP 1 y.wrapTime from errCHDHold AS y where y.errorDesc = 'Invalid Wait Time'),
	x.endTime = @End_Time + (select TOP 1 q.endTime from errCHDHold as q where q.errorDesc = 'Invalid Wait Time'),
	x.talkTime = @Talk_Time + (select TOP 1 w.talkTime from errCHDHold as w where w.errorDesc = 'Invalid Wait Time')
FROM	errCHDHold AS x
WHERE	x.startdate >= DATEADD(DAY, DATEDIFF(DAY, 0, @Current_Date), 0)
	AND x.startdate < DATEADD(DAY, DATEDIFF(DAY, 0, @Current_Date), 1)
	AND x.source Not IN ('ADMIN', 'TRAINING', 'STAFF')
	AND x.errorDesc LIKE 'Invalid Wait%'

E 12°55'05.25"
N 56°04'39.16"

srheal
Starting Member

2 Posts

Posted - 2007-08-22 : 09:07:45

Im trying to get the current table value. say the current wraptime value is 10 I want to use a subquery to get the current value and add it to the @wrap_time variable that is instantiated with the value of 20.
But the subquery return the a multi rows with multi values i need a way to return the count of rows and add 20 plust the current value to each row I might need a update cursor.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 09:13:30

You mean like this?

UPDATE	errCHDHold
SET	wrapTime = wrapTime + @Wrap_Time,
	endTime = endTime + @End_Time,
	talkTime = talkTime + @Talk_Time
WHERE	startdate >= DATEADD(DAY, DATEDIFF(DAY, 0, @Current_Date), 0)
	AND startdate < DATEADD(DAY, DATEDIFF(DAY, 0, @Current_Date), 1)
	AND source Not IN ('ADMIN', 'TRAINING', 'STAFF')
	AND errorDesc LIKE 'Invalid Wait%'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources