日期:2014-05-17  浏览次数:21349 次

求一个Sql
我现在有两张表


A表
keyname alias
1 11
2 22
3 33


B表
name info
22 asdfa 
1 aedf
3 adsf
2 dsdf
3 dasdf
11 dasdf
33 dsfsadf

我现在想做的操作是,通过sql实现:
将B表记录里,Name字段 是 alias的修改成对应的 keyname.

坐等高手解惑



------解决方案--------------------
试试看,没测试过

SQL code

update b
set b.name = (select a.keyname from a where a.alias = b.name)

------解决方案--------------------
SQL code

--等大牛
update B set name = (select keyname from A,B where A.alias=B.name)
where A.alias = B.name;

------解决方案--------------------
SQL code

update b
set b.name = (select a.keyname from a where a.alias = b.name)
where exist (select 1 from a where a.alias = b.name)

------解决方案--------------------
SQL code



update A1,B1 set B1.name = (select keyname from A1,B1 where A1.alias=B1.name)
where A1.alias = B1.name;


update B
set B.name = (select A.keyname from A where A.alias = B.name)
where exists (select 1 from A where A.alias = B.name)


-- 创建数据表
create table A
(
keyname varchar(10),
alias varchar(10)
)

--测试数据

insert into A

select '1','11' union all
select '2','22'  union all
select '3','33'


select * from A


-- 创建数据表
create table B
(
name varchar(10),
info varchar(10)
)

--测试数据

insert into B



select '22','sdf' union all
select '1','sdfsdf'  union all
select '3','sdf'  union all
select '2','dg' union all
select '3','dhrt'  union all
select '11','ree' union all
select '33','sdfs'  

select * from B;

--exists用的不熟 哎

------解决方案--------------------
我现在有两张表


A表
keyname alias
1 11
2 22
3 33


B表
name info
22 asdfa
1 aedf
3 adsf
2 dsdf
3 dasdf
11 dasdf
33 dsfsadf

方法一:
update B as b1, (select a.keyname as aName,b.name as bName from A a,B b where a.alias=b.name) as b2 set b1.name=b2.aName where b1.name=b2.bName;

方法二:
update B as b1,A as a set b1.name=a.keyname where b1.name=a.alias