--1. update 表A set A02=b.tb02 from 表A a left join 表tb b on a.A01=b.A01--2.还是: update 表A set A02='tb02' from 表A a left join 表tb b on a.A01=b.A01--第二种情况没有意思呀,直接变成tb02字符了。
A01 A02 A03 A04 -------------------------- 'a1' '888' '123' 'fk' 'a2' '999' '456' 'fk'update A set a02 = b.tb02 from a , b where a.a01 = b.tb01A01 A02 A03 A04 -------------------------- 'a1' 'tb02' '123' 'fk' 'a2' 'tb02' '456' 'fk'第二种结果是常量值,貌似是个字段名,不对吧. 如果是:则无需要B表了. update A set a02 = 'tb02'
update A set A02 = (case A02 when 'tb02' then b.tb02 when 'tb03' then b.tb03 else '' end)from A a left outer join tb b on a.A01= b.tb01
update A set A02 = (case A02 when 'tb02' then b.tb02 when 'tb03' then b.tb03 else '' end)from A a left outer join tb b on a.A01= b.tb01---测试成功,但还不够通用!如果用VFP,一个&就搞掂!!!
tb表: ------------------------------------- tb01 tb02 tb03 ------------------------------------- a1 888 love a2 999 good A表: ------------------------------------------------------- A01 A02 A03 A04 ------------------------------------------------------- a1 tb03 553 ldld a1 tb02 dlkfk 2241 a2 tb03 6546332 DKLDKD a2 tb02 KDKD 54 update A set A02 = (case A02 when 'tb02' then b.tb02 when 'tb03' then b.tb03 else '' end) from A a left outer join tb b on a.A01= b.tb01select * from A 最后得到的结果: ------------------------------------------------------- A01 A02 A03 A04 ------------------------------------------------------- a1 love 553 ldld a1 888 dlkfk 2241 a2 good 6546332 DKLDKD a2 999 KDKD 54 这样就可以灵活设定!!!
from 表A a left join 表tb b on a.A01=b.A01
不对,我表达错误了,问题应该是这样的:1.有一个表A有多条数据:
A01 A02 A03 A04
--------------------------
'a1' 'A01' '123' 'fk'
'a2' 'A01' '456' 'fk'
...
2.然后有另一个表tb,内容是:
tb01 tb02
----------
a1 '888'
a2 '999'
...
3.表A中字段A02里的内容是字段A01的名称'A01',现想把A02里的内容换成select tb02 from tb 的值,A02字段对应是A01里的值a1,而a1又是另一个tb里的数据,欲得到效果如下:
A01 A02 A03 A04
--------------------------
'a1' 'tb02' '123' 'fk'
'a2' 'tb02' '456' 'fk'
..
不知各位看懂了没有,在线等待
--1.
update 表A set A02=b.tb02
from 表A a left join 表tb b on a.A01=b.A01--2.还是:
update 表A set A02='tb02'
from 表A a left join 表tb b on a.A01=b.A01--第二种情况没有意思呀,直接变成tb02字符了。
--------------------------
'a1' '888' '123' 'fk'
'a2' '999' '456' 'fk'update A set a02 = b.tb02 from a , b where a.a01 = b.tb01A01 A02 A03 A04
--------------------------
'a1' 'tb02' '123' 'fk'
'a2' 'tb02' '456' 'fk'第二种结果是常量值,貌似是个字段名,不对吧.
如果是:则无需要B表了.
update A set a02 = 'tb02'
(case A02
when 'tb02' then b.tb02
when 'tb03' then b.tb03
else ''
end)from A a
left outer join tb b
on a.A01= b.tb01
(case A02
when 'tb02' then b.tb02
when 'tb03' then b.tb03
else ''
end)from A a
left outer join tb b
on a.A01= b.tb01---测试成功,但还不够通用!如果用VFP,一个&就搞掂!!!
-------------------------------------
tb01 tb02 tb03
-------------------------------------
a1 888 love
a2 999 good A表:
-------------------------------------------------------
A01 A02 A03 A04
-------------------------------------------------------
a1 tb03 553 ldld
a1 tb02 dlkfk 2241
a2 tb03 6546332 DKLDKD
a2 tb02 KDKD 54
update A set A02 =
(case A02
when 'tb02' then b.tb02
when 'tb03' then b.tb03
else ''
end)
from A a
left outer join tb b
on a.A01= b.tb01select * from A
最后得到的结果:
-------------------------------------------------------
A01 A02 A03 A04
-------------------------------------------------------
a1 love 553 ldld
a1 888 dlkfk 2241
a2 good 6546332 DKLDKD
a2 999 KDKD 54 这样就可以灵活设定!!!