从一张表中update数据到另一张表中!诸位大大帮忙琢磨琢磨
从一张表中update数据到另一张表中!!!!急!各位大大帮忙琢磨琢磨
表A 表B
表A
a1 a2
a001
a002 2005
a001 2012
a002 2012
a003 2012
a001
a004
a004 2007
a004
表B
b1 b2
a001 2012
a001 2013
a004 2005
a004 2006
a004 2007
表A中a2有空值,表B中没有,a1=b1通过sql语句怎么把表B的b2数据update到表a中,使他们的数据一致?
------解决方案--------------------
表A 表B
表A
a1 a2
a001
a002 2005
a001 2012
a002 2012
a003 2012
a001
a004
a004 2007
a004
表B
b1 b2
a001 2012
a001 2013
a004 2005
a004 2006
a004 2007
表A中a2有空值,表B中没有,a1=b1通过sql语句怎么把表B的b2数据update到表a中,使他们的数据一致?
SQL
------解决方案--------------------
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-05 17:06:19
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据:#表A
if object_id('tempdb.dbo.#表A') is not null drop table #表A
go
create table #表A([a1] varchar(4),[a2] int)
insert #表A
select 'a001',null union all
select 'a002',2005 union all
select 'a001',2012 union all
select 'a002',2012 union all
select 'a003',2012 union all
select 'a001',null union all
select 'a004',null union all
select 'a004',2007 union all
select 'a004',NULL
--> 测试数据:#表B
if object_id('tempdb.dbo.#表B') is not null drop table #表B
go
create table #表B([b1] varchar(4),[b2] int)
insert #表B
select 'a001',2012 union all
select 'a001',2013 union all