How to update a table with a select statement?

Transact-SQL
,
1

I am trying to calculate a moving St. deviation from one column and then update the last column of that table with the results but when I declare @sql ( variable) as money it returns me error while declaring @sql (variable) as nvarchar(1000) works but then I can't update the table with the variable because the column is defined as money.

Here is the code:

                 declare @sql as nvarchar (1000)

                 set @sql ='
                  SELECT 
                  [Dates],  [Spread_5M1],
              [Stdev36M]=CASE WHEN ROW_NUMBER() OVER (ORDER BY [Dates] ) > 36
                  THEN STDEVP([Spread_5M1])
                  OVER (ORDER BY [Dates] ROWS BETWEEN 36 PRECEDING AND CURRENT ROW) 
		  END

                     FROM Momentum_STdev'


                    execute (@sql)


                   update Momentum_stdev

                 set [Stdev36M]= @sql

                 where [Dates] = '2008-09-30';

it retuns me this:

                                    (121 row(s) affected)
                                  Msg 235, Level 16, State 0, Line 19
                                 Cannot convert a char value to money. The char value has incorrect syntax.

Does someone have a clue to solve this?

2

You cannot use dynamic SQL the way you are attempting to do it. SQL syntax just doesn't allow it. Is there a reason for using the dynamic SQL statement?

Instead try the following. It should compile (but I don't know if that is the logic you want to implement, so try it in a testing environment)

;WITH cte AS
(
	SELECT  [Dates] ,
        [Spread_5M1] ,
        [Stdev36M] = CASE WHEN ROW_NUMBER() OVER ( ORDER BY [Dates] ) > 36
          THEN STDEVP([Spread_5M1]) 
			OVER ( ORDER BY [Dates] ROWS BETWEEN 36 PRECEDING AND CURRENT ROW )
                     END
	FROM    Momentum_STdev
)
UPDATE ms SET 
	[Stdev36M] = c.[Stdev36M]
FROM
	Momentum_STdev ms
	INNER JOIN cte c ON 
		c.Dates = ms.Dates;
1 Like
3

it worked ! thanks a lot :smiley: