update Tabel_A a set a.Tabel_A_key =(select c.Tabel_C_min from Tabel_b b, Tabel_C c where c.Tabel_C_min <> '' and a.Tabel_A_a = c.Tabel_C_a and b.Tabel_b_b = c.Tabel_C_b) where a.Tabel_A_size = '6';
楼上的改得不错 但是c.Tabel_C_min <> '' 要改成c.Tabel_C_min is not null 否则这个条件恒不成立
update tanle_a a set a.table_a_key = ( select d.table_c_min from ( select c.table_c_a, c.table_min from table_b b, table_c c where b.table_b = c.table_c_b and c.table_c_min is not null ) d where a.table_a_a = d.table_c_a ) where a.table_a_size = '6';
把 1# 的基础上,在where 后再加一个条件 , 不然匹配不上的记录,会被更新成 null 值 。 拷一下 1# 的代码。update Tabel_A a set a.Tabel_A_key =(select c.Tabel_C_min from Tabel_b b, Tabel_C c where c.Tabel_C_min is not null and a.Tabel_A_a = c.Tabel_C_a and b.Tabel_b_b = c.Tabel_C_b) where a.Tabel_A_size = '6' and exists(select * from Tabel_b b, Tabel_C c where c.Tabel_C_min is not null and a.Tabel_A_a = c.Tabel_C_a and b.Tabel_b_b = c.Tabel_C_b)
oracle 处理这种问题没有mssql好用,呵,上面的方法算一种,下面还有两种,可以测试一下:--方法2: update (select a. Tabel_A_key, c.Tabel_C_min from Tabel_A a, Tabel_b b, Tabel_C c where a.Tabel_A_size = '6' and c.Tabel_C_min is not null and a.Tabel_A_a = c.Tabel_C_a and b.Tabel_b_b = c.Tabel_C_b) set Tabel_A_key = Tabel_C_min; --方法3: merge into emp a using (select c.Tabel_C_min, c.Tabel_C_a from Tabel_b b, Tabel_C c where c.Tabel_C_min is not null and b.Tabel_b_b = c.Tabel_C_b) c on (a.Tabel_A_a = c.Tabel_C_a and a.Tabel_A_size = '6') when matched then update set Tabel_A_key = c.Tabel_C_min;
update Tabel_A a
set a.Tabel_A_key =(select c.Tabel_C_min
from Tabel_b b, Tabel_C c where c.Tabel_C_min <> ''
and a.Tabel_A_a = c.Tabel_C_a
and b.Tabel_b_b = c.Tabel_C_b)
where a.Tabel_A_size = '6';
但是c.Tabel_C_min <> '' 要改成c.Tabel_C_min is not null
否则这个条件恒不成立
set a.table_a_key = ( select d.table_c_min
from ( select c.table_c_a, c.table_min
from table_b b, table_c c
where b.table_b = c.table_c_b
and c.table_c_min is not null ) d
where a.table_a_a = d.table_c_a )
where a.table_a_size = '6';
拷一下 1# 的代码。update Tabel_A a
set a.Tabel_A_key =(select c.Tabel_C_min
from Tabel_b b, Tabel_C c where c.Tabel_C_min is not null
and a.Tabel_A_a = c.Tabel_C_a
and b.Tabel_b_b = c.Tabel_C_b)
where a.Tabel_A_size = '6'
and exists(select *
from Tabel_b b, Tabel_C c where c.Tabel_C_min is not null
and a.Tabel_A_a = c.Tabel_C_a
and b.Tabel_b_b = c.Tabel_C_b)
update (select a. Tabel_A_key, c.Tabel_C_min
from Tabel_A a, Tabel_b b, Tabel_C c
where a.Tabel_A_size = '6'
and c.Tabel_C_min is not null
and a.Tabel_A_a = c.Tabel_C_a
and b.Tabel_b_b = c.Tabel_C_b)
set Tabel_A_key = Tabel_C_min;
--方法3:
merge into emp a
using (select c.Tabel_C_min, c.Tabel_C_a
from Tabel_b b, Tabel_C c
where c.Tabel_C_min is not null
and b.Tabel_b_b = c.Tabel_C_b) c
on (a.Tabel_A_a = c.Tabel_C_a and a.Tabel_A_size = '6')
when matched then
update set Tabel_A_key = c.Tabel_C_min;