update a set a.Facontent=b.Facontent from tb a inner join tb2 b on a.id=b.id where a.id=1 and b.id=1
update tb set Facontent=(select top 1 Facontent from tb2 where ID=1) where ID=1
--如果 Facontent 不超過8000 cast(Facontent as varchar(8000))
update tb set Facontent=(select top 1 Facontent from tb2 where ID=1) where ID=1 在这一子查询或聚合表达式中,text、ntext 和 image 数据类型无效。
DECLARE @TA TABLE(ID INT, COL TEXT) INSERT @TA SELECT 1, 'AAAAA' DECLARE @TB TABLE(ID INT, COL TEXT) INSERT @TB SELECT 1, 'BBBBB'UPDATE @TA SET COL=B.COL FROM @TA AS A JOIN @TB AS B ON A.ID=B.IDSELECT * FROM @TA /* ID COL ----------- ------------------------------------------------------ 1 BBBBB */
更新text数据用uptext,如果用来更新的数据不是text 可以用变量接收;如果也是text则要用两个指针,如下: DECLARE @v1 varbinary(16),@v2 varbinary(16) SELECT @v1 = TEXTPTR(tb.Facontent),@v2 = TEXTPTR(tb2.Facontent) FROM tb,tb2 where tb.ID=1 and tb2.id=1UPDATETEXT tb.Facontent @v1 0 null tb2.Facontent @v2
--更新text字段的值 create table PE_Soft(SoftIntro text,ChannelID integer) insert into PE_Soft select 'aaa ',1004 union all select 'bbb ',1003 union all select 'ccc ',1002 declare @ptr binary(16) select @ptr=textptr(SoftIntro) from PE_Soft where ChannelID=1002 updatetext PE_Soft.SoftIntro @ptr null 0 'testing ' select * from PE_Soft drop table PE_Soft
set a.Facontent=b.Facontent
from tb a inner join tb2 b
on a.id=b.id
where a.id=1 and b.id=1
--如果 Facontent 不超過8000
cast(Facontent as varchar(8000))
在这一子查询或聚合表达式中,text、ntext 和 image 数据类型无效。
INSERT @TA
SELECT 1, 'AAAAA'
DECLARE @TB TABLE(ID INT, COL TEXT)
INSERT @TB
SELECT 1, 'BBBBB'UPDATE @TA SET COL=B.COL
FROM @TA AS A JOIN @TB AS B ON A.ID=B.IDSELECT * FROM @TA
/*
ID COL
----------- ------------------------------------------------------
1 BBBBB
*/
DECLARE @v1 varbinary(16),@v2 varbinary(16)
SELECT @v1 = TEXTPTR(tb.Facontent),@v2 = TEXTPTR(tb2.Facontent) FROM tb,tb2 where tb.ID=1 and tb2.id=1UPDATETEXT tb.Facontent @v1 0 null tb2.Facontent @v2
create table PE_Soft(SoftIntro text,ChannelID integer)
insert into PE_Soft
select 'aaa ',1004
union all
select 'bbb ',1003
union all
select 'ccc ',1002 declare @ptr binary(16)
select @ptr=textptr(SoftIntro) from PE_Soft where ChannelID=1002
updatetext PE_Soft.SoftIntro @ptr null 0 'testing '
select * from PE_Soft drop table PE_Soft