Update statement problems - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Update statement problems

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2002-08-03 : 10:11:03

I am trying to create a dynamic update statement, but am having a little problem.

The standard statement would be.

UPDATE SL_ACCOUNTS SET CUTURNOVR_L7 = CUTURNOVR_LT - 200 WHERE CUCODE = 'MYCODE'

What I am trying to do is substitute L with @year and 7 with @period
As these values will differ depending a number of select statements earler on in my script.

This is what I tried, but the Syntax is not correct

UPDATE SL_ACCOUNTS SET CUTURNOVR_ + @year + @period = (CUTURNOVR_ + @year + @period) - 200 WHERE CUCODE = 'MYCODE'

@year and @period are set to VARCHAR(2)
but the CUTURNOVR_L7 field etc are integer fields

Can anyone point me in the right direction

Paul

Nazim
A custom title

1408 Posts

Posted - 2002-08-03 : 11:04:45

you can achieve this by using Dynamic Sql . this should teach you how to do it http://www.sqlteam.com/item.asp?ItemID=4619 .

Anywayz , for your current prob . this should help you.

declare @sql varchar(500)
@sql='UPDATE SL_ACCOUNTS SET CUTURNOVR_ '+ @year + @period +'= (CUTURNOVR_' +@year + @period +' ) - 200 WHERE CUCODE = ''MYCODE'' '
exec (@sql)

HTH

EDIT: Another week without a Title

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Edited by - Nazim on 08/03/2002 11:06:14

wotrac
Yak Posting Veteran

98 Posts

Posted - 2002-08-03 : 15:23:00

Hi Nazim

Ignore my earlier message.

I am still getting a syntax problem with the code you suggested.

declare @sql varchar(500)
@sql='UPDATE SL_ACCOUNTS SET CUTURNOVR_ '+ @year + @period +'= (CUTURNOVR_' +@year + @period +' ) - 200 WHERE CUCODE = '@oldAccnt''
exec (@sql)

Syntax error near '='

Paul

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-08-03 : 15:33:00

declare @sql varchar(500)
@sql='UPDATE SL_ACCOUNTS SET CUTURNOVR_ '+ @year + @period +'= (CUTURNOVR_' +@year + @period +' ) - 200 WHERE CUCODE = ''' + @oldAccnt + '''
exec (@sql)

This assumes that @oldAccnt is declared as varchar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources