5楼的SQL需要改进一下 UPDATE a SET cj1 = (SELECT cj FROM b WHERE a.xh = b.xh) WHERE EXISTS (SELECT 1 FROM b WHERE a.xh = b.xh)
merge into A using B on(A.xh=B.xh) when matched then --有则更新 update set A.cj1=B.cj1; when not matched then --无则插入 insert values(B.xh,B.cj1);A(xh,cj1)表中的cj1替换为B(xh,cj)中的cj,通过xh
UPDATE a SET cj1 = (SELECT cj FROM b WHERE a.xh = b.xh) WHERE EXISTS (SELECT 1 FROM b WHERE a.xh = b.xh)
9和11楼的对 UPDATE A SET A.cj1 = (SELECT B.cj FROM B WHERE A.xh = B.xh) WHERE EXISTS (SELECT 1 FROM B WHERE A.xh = B.xh)
update a set cj1=(select cj from b where a.xh=b.xh)
如果 在A,B表中xh都是主键的话可以使用如下语句update (select a.xh,a.cj1,b.cj from A,B where A.xh=b.xh) set cj1=cj
支持此哥们,Merge into 判断是否合符你的要求,符合则更新,不符合还可以采取insert操作;
merge into t_table t using(select id,name,age,re,t_id from t_table_1) t1 on(t.id=t1.id) when matched then update set t.name=t1.name,t.age=t1.age,t.re=t1.re,t.t_id=t1.t_id(需要更新字段) when not matched then insert (t.id,t.name,t.age,t.re,t.t_id) values(t1.id,t1.name,t1.age,t1.re,t1.t_id) 你可以按以上的格式自己测试一哈...
UPDATE A SET cj1 = (SELECT cj FROM B WHERE A.xh = B.xh); 试试看,估计能解决!如果不能告诉我 下啊
from a,b
where a.xh=b.xh
这样么,什么需求?为什么这么做?
A:
xh cj1
001
002
003B:
xh cj
001 100
002 200
003 300结果:
A:
xh cj1
001 100
002 200
003 300
update a set a.cj1=(select b.cj from b where b.xh=a.xh)
SET cj1 = (SELECT cj FROM b WHERE a.xh = b.xh)
WHERE EXISTS (SELECT 1 FROM b WHERE a.xh = b.xh)
merge into A
using B
on(A.xh=B.xh)
when matched then --有则更新
update set A.cj1=B.cj1;
when not matched then --无则插入
insert values(B.xh,B.cj1);A(xh,cj1)表中的cj1替换为B(xh,cj)中的cj,通过xh
SET cj1 = (SELECT cj FROM b WHERE a.xh = b.xh)
WHERE EXISTS (SELECT 1 FROM b WHERE a.xh = b.xh)
UPDATE A
SET A.cj1 = (SELECT B.cj FROM B WHERE A.xh = B.xh)
WHERE EXISTS (SELECT 1 FROM B WHERE A.xh = B.xh)
update a set cj1=(select cj from b where a.xh=b.xh)
set cj1=cj
merge into t_table t
using(select id,name,age,re,t_id from t_table_1) t1
on(t.id=t1.id)
when matched then
update set t.name=t1.name,t.age=t1.age,t.re=t1.re,t.t_id=t1.t_id(需要更新字段)
when not matched then insert (t.id,t.name,t.age,t.re,t.t_id)
values(t1.id,t1.name,t1.age,t1.re,t1.t_id)
你可以按以上的格式自己测试一哈...
试试看,估计能解决!如果不能告诉我 下啊