Syntax For Update Statement Please

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 2005 Forums

Transact-SQL (2005)

Syntax For Update Statement Please

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-01 : 03:43:03

Hello,

I am in Query Analyser trying to sort out an update statement, but am having trouble with the syntax.

I have the following :


update dbo.tbl_EmployeeDetails
set  EEMemo = 
( select
EEMemo
from 
dbo.Staging_EmployeeDetails
where AutoRef = dbo.tbl_EmployeeDetails.TechnicianID)

But am getting the following error :

"Server: Msg 279, Level 16, State 3, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression."

How do I get round this ?

Thanks in advance.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-01 : 03:52:12

update dbo.tbl_EmployeeDetails
set EEMemo = s.EEMemo
from
dbo.Staging_EmployeeDetails s join dbo.tbl_EmployeeDetails e
on s.AutoRef = e.TechnicianID

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-01 : 03:53:08

See this: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57445[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-01 : 04:00:42

Thanks harsh_athalye - that worked a treat.

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 05:23:45

"update dbo.tbl_EmployeeDetails
set EEMemo = s.EEMemo
from
dbo.Staging_EmployeeDetails s join dbo.tbl_EmployeeDetails e
on s.AutoRef = e.TechnicianID"

Does that work with an Alias? I would have expected that you would have to do

UPDATE e
...

And personally I always use an alis for UPDATE commands, even if there are no JOINs:

UPDATE U
SET MyCol = ...
FROM MyTable AS U
JOIN OtherTable AS X
ON ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources