这样我试过,好像不行,这样的结果是A中两条记录全部被B中分别update 我想要的是这样的结果,可是oracle不支持 UPDATE A SET YE=B.YE FROM A INNER JOIN B ON A.XMDH=B.XMDH
update a set ye=b.ye from a where a.xmdh=b.xmdh
update a set a.ye=(select b.ye from a, b where b.xmdh=a.xmdh) where b.xmdh=a.xmdh
也不行啊, 1.update a set a.ye=(select b.ye from a, b where b.xmdh=a.xmdh) 2.where b.xmdh=a.xmdh 他报错说第二行的b.xmdh是invalid identifier 我用的是oracle9iserver和8.0.5的client 不知道是不是有关系?
update a tab1 set tab1.ye = ( select tab2.ye from b tab2 where tab2.xmdh = tab1.xmdh and rownum = 1 )
用相关子查询,应该这样 update a set a.ye=(select b.ye from b where b.xmdh=a.xmdh)
9i里有个merge吧, merge into using 'sql语句' when具体语法不是很清楚!查一下资料!
他所提的问题用update就可以了 merge是针对同时可修改和插入的情况的。
MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*0.1);
我想要的是这样的结果,可是oracle不支持
UPDATE A
SET YE=B.YE
FROM A INNER JOIN B ON A.XMDH=B.XMDH
where b.xmdh=a.xmdh
1.update a set a.ye=(select b.ye from a, b where b.xmdh=a.xmdh)
2.where b.xmdh=a.xmdh
他报错说第二行的b.xmdh是invalid identifier
我用的是oracle9iserver和8.0.5的client
不知道是不是有关系?
set tab1.ye =
(
select tab2.ye from b tab2
where tab2.xmdh = tab1.xmdh
and rownum = 1
)
update a set a.ye=(select b.ye from b where b.xmdh=a.xmdh)
merge into
using 'sql语句'
when具体语法不是很清楚!查一下资料!
merge是针对同时可修改和插入的情况的。
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);