求一条sql 语句，再线等！谢谢！
我有三个表：
A表 ： aid,orderno,uid
1 0001 1
2 0002 C01
B表 ： bid,bname
1 BBBB
C表： cid,cname
C01 CCCC
把A表中的uid换成B表中的bname或者C表的cname
这个可以实现吗？怎么实现？
请各位大侠赐教！谢谢！
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-06 15:22:27
-- 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]([aid] int,orderno varchar(4),uid varchar(3))
insert [A]
select 1,'0001','1' union all
select 2,'0002','C01'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([bid] varchar(3),bname varchar(4))
insert [B]
select 1,'BBBB'
--> 测试数据：[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([cid] varchar(3),cname varchar(4))
insert [C]
select 'C01','CCCC'
--------------开始查询--------------------------
select a.aid,a.orderno,ISNULL(bname,cname)
from [A] left JOIN b ON a.uid=b.bid
left JOIN c ON a.uid=c.cid
----------------结果----------------------------
/*
aid orderno
----------- ------- ----
1 0001 BBBB
2 0002 CCCC
*/
update A e set uid=d.bname or uid=d.cname from(select * from A a,B b,C c where a.uid=b.bid and a.uid=c.cid) d
where e.uid=d.bid or e.uid=d.cid
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-06 15:22:27
-- 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]([aid] int,orderno varchar(4),uid varchar(5))
insert [A]
select 1,'0001','1' union all
select 2,'0002','C01'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([bid] varchar(3),bname varchar(4))
insert [B]
select 1,'BBBB'
--> 测试数据：[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([cid] varchar(3),cname varchar(4))
insert [C]
select 'C01','CCCC'
--------------开始查询--------------------------
select a.aid,a.orderno,ISNULL(bname,cname) AS [uid]
from [A] left JOIN b ON a.uid=b.bid
left JOIN c ON a.uid=c.cid
--更新：
SELECT * FROM a
;WITH cte AS (select a.aid,a.orderno,ISNULL(bname,cname)uid
from [A] left JOIN b ON a.uid=b.bid
left JOIN c ON a.uid=c.cid
)
UPDATE a
SET a.uid=cte.uid
FROM cte
WHERE a.aid=cte.aid
--检查
SELECT * FROM a
----------------结果----------------------------
/*
aid orderno uid
----------- ------- -----
1 0001 1
2 0002 C01
aid orderno uid
----------- ------- -----
1 0001 BBBB
2 0002 CCCC
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([aid] int,orderno varchar(4),uid varchar(5))
insert [A]
select 1,'0001','1' union all
select 2,'0002','C01'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([bid] varchar(3),bname varchar(4))
insert [B]
select 1,'BBBB'
--> 测试数据：[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([cid] varchar(3),cname varchar(4))
insert [C]
select 'C01','CCCC'
;with ceb as
(
select a.aid,a.orderno ,a.uid,b.bid,b.bname
from A a,B b where a.uid=b.bid
union all
select a.aid,a.orderno ,a.uid,c.cid,c.cname
from A a,C c where a.uid=c.cid
)
update A set A.uid=d.bname
from ceb d
where A.uid=d.bid
