一条简单的Update 马上结贴 在线等

[Transitional]

A表：
UnitID Remark
1
2
3
B表：
UnitID Remark
1 a
2 b
3 c


求一条Update语句后的结果
--update ？？

A表：
UnitID Remark
1 a
2 b
3 c

[發糞塗牆]

update a set a.remark=b.remark from a inner join b on a.unitID=b.unitID

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-27 16:52:12
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([UnitID] int,[Remark] sql_variant)
insert [A]
select 1,null union all
select 2,null union all
select 3,null
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([UnitID] int,[Remark] varchar(1))
insert [B]
select 1,'a' union all
select 2,'b' union all
select 3,'c'
--------------开始查询--------------------------

select * from [A]

update a
set a.remark=b.remark
from a inner join b on a.unitID=b.unitID

select * from [A]
----------------结果----------------------------
/* 
UnitID      Remark
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           NULL
2           NULL
3           NULL

(3 row(s) affected)

(3 row(s) affected)

UnitID      Remark
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           a
2           b
3           c
*/

[--小F--]

TRUNCATE TABLE A INSERT INTO A SELECT * FROM B

[唐诗三百首]

update a
 set a.Remark=(select top 1 Remark from B表 b 
               where b.UnitID=a.UnitID)
 from A表 a 

[LongRui888]

update a
set Remark = b.Remark
from b
where a.UnitID = b.UnitID

[LongRui888]

引用 3 楼 fredrickhu 的回复:

TRUNCATE TABLE A INSERT INTO A SELECT * FROM B

你这方法好

[水族杰纶]

引用 3 楼 fredrickhu 的回复:

TRUNCATE TABLE A INSERT INTO A SELECT * FROM B

这招狠啊

[水族杰纶]

引用 6 楼 yupeigu 的回复:

[quote=引用 3 楼 fredrickhu 的回复:] TRUNCATE TABLE A INSERT INTO A SELECT * FROM B

你这方法好[/quote] 直接drop A sp_rename B 得了

[LongRui888]

引用 8 楼 wufeng4552 的回复:

[quote=引用 6 楼 yupeigu 的回复:] [quote=引用 3 楼 fredrickhu 的回复:] TRUNCATE TABLE A INSERT INTO A SELECT * FROM B

你这方法好[/quote] 直接drop A sp_rename B 得了[/quote] 呵呵，是啊

[max_min_]

放眼望去，全是版主。

[无涯大者]

引用 3 楼 fredrickhu 的回复:

TRUNCATE TABLE A INSERT INTO A SELECT * FROM B

Up,学习了!