update statements in dynamic sql

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

update statements in dynamic sql

Author

Topic

rajesh_kanthaswami
Starting Member

3 Posts

Posted - 2009-02-27 : 06:08:55

can we update a table in dynamic way?
i wrote the below query in procedure but it throwed a error saying it is invalid..
the query is

select @string = 'update a
set a.operatorin = b.operatorin
from radoncda_pre a, calling.margin.dbo.cda b
where a.sessionida = b.sessionida'
exec @string

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-02-27 : 06:47:56

Yes, but you need to check the syntax of your update statement. You can not alias the tablename of the table you are updating.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-27 : 07:22:48

yes you can do this

I think the syntax error is this:

EXEC @string should be :

EXEC (@sql) (note the brackets)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-27 : 07:24:56

Also RickD

Yes -- you *can* reference tables by alias for an update (or a delete)


CREATE TABLE #foo (
	[bar] INT
	)

INSERT #foo ([bar])
      SELECT 10
UNION SELECT 20

SELECT * FROM #foo

UPDATE f
SET [bar] = 0
FROM #foo f
WHERE
	f.[bar] = 20

SELECT * FROM #foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources