May 16, 2007 at 7:44 am
I am trying to grab the results of a select statement (max(date)) and use it to update another table, i.e.,
Declare @DateVar varchar(10)
Set @DateVar = convert(varchar(11),(getdate()-5), 20)
update dbo.DataDates
set EndDate = max(mydate) from dbo.myData
where mydate > ''+@DateVar+''
Of course this returns an error "An aggregate may not appear in the set list of an UPDATE statement."
My next thought was try and group everything together in one huge statement,
Declare @DateVar varchar(10)
Declare @NewDate varchar(20)
Declare @Statement nvarchar(2000)
--SET @NewDate = @DateVar -- To test PRINT only
Set @DateVar = convert(varchar(11),(getdate()-5), 20)
SET @Statement = 'Declare @NewDate varchar(20) ;
select @NewDate = max(mydate) from dbo.myData
where mydate > '''+@DateVar+''' ;
update dbo.DataDates
set EndDate = '''+@NewDate+''''
--PRINT @Statement
EXEC sp_executesql @Statement
The command appears to complete successfully, but nothing is updated. I thought that if I declared the variable within the "@Statement", it would be in scope for the entire sp_executesp scope?
Basically, I am processing a lot of data weekly via a scheduled SP, then I need to grab the last date from the table (myData) used for processing and put it into another table (DataDates) for reporting purposes.
Any ideas?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
May 16, 2007 at 8:10 am
What about:
UPDATE
[Northwind].[dbo].[Orders]
SET [ShippedDate] = x.myshipdate
FROM (SELECT MAX([ShippedDate]) AS myshipdate
FROM
[Orders]) x
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2007 at 12:15 pm
Actually, you are right... I could have just done a SELECT to set the variable:
Declare @DateVar varchar(10)
Declare @NewDate varchar(20)
Declare @Statement nvarchar(2000)
Set @DateVar = convert(varchar(11),(getdate()-5), 20)
select @NewDate = max(mydate) from dbo.myData
where mydate > ''+@DateVar+''
update dbo.DataDates
set EndDate = ''+@NewDate+''
I get so use to trying to put all of my SQL statements into procedures using "EXEC sp_executesql..." that I forgot it is possible to use regular SELECT Statements in SPs.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply