从一张表中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中，使他们的数据一致? 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
select 'a004',2005 union all
select 'a004',2006 union all
select 'a004',2007
--------------开始查询--------------------------
--select * from #表B
--------------开始查询--------------------------
--select * from #表A
UPDATE a
SET a.a2=b.b2
FROM #表A a INNER JOIN #表B b ON a.a1=b.b1
WHERE a.a2 IS NULL
----------------结果----------------------------
/*
a1 a2
---- -----------
a001 2012
a002 2005
a001 2012
a002 2012
a003 2012
a001 2012
a004 2005
a004 2007
a004 2005
*/
if object_id('tempdb.dbo.#tbA') is not null drop table #表A
go
create table #tbA([a1] varchar(4),[a2] int)
insert #tbA
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
if object_id('tempdb.dbo.#tbB') is not null drop table #表B
go
create table #tbB([b1] varchar(4),[b2] int)
insert #tbB
select 'a001',2012 union all
select 'a001',2013 union all
select 'a004',2005 union all
select 'a004',2006 union all
select 'a004',2007
;WITH cte AS
(
SELECT
a.a1, a.a2, newa2 = COALESCE(b.b2, c.cb2)
FROM
(SELECT rowid=ROW_NUMBER() OVER(PARTITION BY a1 ORDER BY GETDATE()),* FROM #tbA WHERE a2 IS NULL) a
LEFT JOIN
(
SELECT rowid=ROW_NUMBER() OVER(PARTITION BY b1 ORDER BY GETDATE()),*
FROM #tbB m
WHERE not EXISTS(SELECT 1 FROM #tbA n WHERE n.a1=m.b1 AND n.a2=m.b2)
) b
ON a.a1 = b.b1 AND a.rowid=b.rowid
CROSS APPLY
(
SELECT cb2 = MAX(b2) FROM #tbB m WHERE m.b1 = a.a1
) C
)
UPDATE cte
SET a2 = newa2
/*
a1a2
a0012013
a0022005
a0012012
a0022012
a0032012
a0012013
a0042005
a0042007
a0042006
*/