求：SQL？？？(100分)

我有两个表，table1(id,name,memo),table2(id,work)，table1.id=table2.id
现在想用一条UPDATE语句将table1.memo字段更新成table2.work
我使用的是SQL Server2000。
我好象记得在Oracle中是
update table1 ta
set memo=(select work from table2 tb where ta.id=tb.id);
在SQL Server中好象不行。

？？

update table1 set memo=(select "work" from table2 tb where table1.id=tb.id)

update table1 set memo=(select work from table2 tb where table1.id=tb.id)
~~~~~~~

同意sportsman
也可以
update table1 set memo=(select work from table2 tb,table1 ta where ta.id=tb.id)
但注意select 出来的结果只能有一个值，如果加上distinct是更保险

update table1 set table1.memo=table2.work from table1,table2 where table1.id=table2.id

SET QUOTED_IDENTIFIER ON;
update table1 set memo="work" from table1,table2 where table1.id=table2.id;

不过似乎Text或Image字段不能这样用的。

SE pubs

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

GO

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(pr_info)

FROM pub_info pr, publishers p

WHERE p.pub_id = pr.pub_id

AND p.pub_name = 'New Moon Books'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO
或

USE pubs

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

GO

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(pr_info)

FROM pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 88 1 'b'
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO

多人接受答案了。