我有三个表：
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