---------------------------------------------------------------- -- 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,[Re] 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,[Re] varchar(1)) insert [B] select 1,'a' union all select 2,'b' union all select 3,'c' --------------开始查询--------------------------select * from [A]update a set a.re=b.re from a inner join b on a.unitID=b.unitIDselect * from [A] ----------------结果---------------------------- /* UnitID Re ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 NULL 2 NULL 3 NULL(3 row(s) affected)(3 row(s) affected)UnitID Re ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 a 2 b 3 c */
TRUNCATE TABLE AINSERT INTO A SELECT * FROM B
update a set a.Re=(select top 1 Re from B表 b where b.UnitID=a.UnitID) from A表 a
update a set Re = b.Re from b where 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,[Re] 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,[Re] varchar(1))
insert [B]
select 1,'a' union all
select 2,'b' union all
select 3,'c'
--------------开始查询--------------------------select * from [A]update a
set a.re=b.re
from a inner join b on a.unitID=b.unitIDselect * from [A]
----------------结果----------------------------
/*
UnitID Re
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 NULL
2 NULL
3 NULL(3 row(s) affected)(3 row(s) affected)UnitID Re
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a
2 b
3 c
*/
update a
set a.Re=(select top 1 Re from B表 b
where b.UnitID=a.UnitID)
from A表 a
set Re = b.Re
from b
where a.UnitID = b.UnitID
你这方法好
直接drop A
sp_rename B 得了
你这方法好
直接drop A
sp_rename B 得了呵呵,是啊