我现在有两张表
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.坐等高手解惑
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.坐等高手解惑
update b
set b.name = (select a.keyname from a where a.alias = b.name)
--等大牛
update B set name = (select keyname from A,B where A.alias=B.name)
where A.alias = B.name;
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)
--4楼 existsupdate 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)
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 Aselect '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 Bselect '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用的不熟 哎
2 sdf
1 sdfsdf
3 sdf
2 dg
3 dhrt
1 ree
3 sdfs
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
select a.keyname from a where a.alias = b.name 这个检索起来有三行,三行赋值一行肯定不行