表一有AB字段,表二也有对应的AB字段,现在表一的B字段是空的。要将表二中对应的B字段填入表一中(根据A字段对应)
以下网上的方法已经用过了:不行。有更好的方法吗update mobile_province1 set province_code=b.province_code
from mobile_province1 a inner join mobile_province b on a.mobile_prefix=b.mobile_prefix
update mobile_province1 left join mobile_province on mobile_province1.mobile_prefix=mobile_province.mobile_prefix
set mobile_province1.province_code=mobile_province.province_code
以下网上的方法已经用过了:不行。有更好的方法吗update mobile_province1 set province_code=b.province_code
from mobile_province1 a inner join mobile_province b on a.mobile_prefix=b.mobile_prefix
update mobile_province1 left join mobile_province on mobile_province1.mobile_prefix=mobile_province.mobile_prefix
set mobile_province1.province_code=mobile_province.province_code
set table1.B = table2.B
from table1,table2
where table1.A=table2.A
看看
set table1.B = table2.B
from table1,table2
where table1.A=table2.A
用以下 的SQL语句,提示错误: sql comman not properly end
update mobile_province1 set a.province_code=b.province_code
from mobile_province1 a , mobile_province b where a.mobile_prefix=b.mobile_prefix
from mobile_province b where b.mobile_prefix = mobile_province1.mobile_prefix
update mobile_province1 a set province_code=b.province_code
from mobile_province b
where a.mobile_prefix=b.mobile_prefix
SET province_code=(SELECT province_code FROM mobile_province WHERE mobile_province1.mobile_prefix=mobile_province.mobile_prefix)
from table2 where table2.A = table1.A
我的数据库是ORACLE 用以下 的SQL语句,提示错误: sql comman not properly end
set table1.B=(select table2.B from table2 where table2.A=table1.A)
where exists (select 1
from table1
where table1.A=table2.A
)
SET province_code=(SELECT province_code FROM mobile_province WHERE mobile_province1.mobile_prefix=mobile_province.mobile_prefix)
我表里有一万多条数据,运行你这个SQL语句,半天没动静,最后一查,一条都没有UPDATE。
update mobile_province1 a
set province_code=(select b.province_code from mobile_province b where b.mobile_prefix=a.mobile_prefix)
where exists (select 1
from mobile_province b
where b.mobile_prefix=a.mobile_prefix
)
这个对b表扫描了两遍,
还可以这样写
一个替代的方法可以采用:
update mobile_province1 a
set province_code=nvl((select b.province_code from mobile_province b where b.mobile_prefix=a.mobile_prefix),a.province_code )如果a表中更新的数据不是很多,建议用cursor以提高性能
from table1
inner join table2 on table2.A = table1.A
这样肯定可以的。
update
(select a.province_code as province_code, b.province_code as provincecode from mobile_province1 a join mobile_province b on a.mobile_prefix=b.mobile_prefix )
set province_code = provincecode