用触发器
子查询最好不要用在UPDATE等DDL语句中
子查询最好不要用在UPDATE等DDL语句中
解决方案 »
- 在插入时出现"ORA-00947: 没有足够的值"的错误!
- sqlserver转oracle时,group by 不能使用序列的解决办法?
- ORACEL 强人请进!!!!!!!!!
- 求一联合sql语句,谢谢大家.
- 小菜完全没思路, Oracle 报表统计问题
- 两个表间的 update 问题
- oracle8i监听器出错!
- 从ORACLE中文版里导出,导入ORACLE英文版后,汉字为乱码,咋办?
- Oracle的文档是在哪安装的?
- 请问 pl / sql 执行SQL出现 Write of address 05007468 是什么意思 ><?
- ORACLE触发器如何判断它更新了或没有更新某列?
- ORACLE9i备份及恢复,超紧急!
SET A.name = (
SELECT B.NAME
FROM B
WHERE A.a_id = B.a_id AND B.b_id = '单号');
这样就行了吧
create trigger name_tri
before update on b
for each row
as
begin
update a set name=:new.name where a_id = :new.a_id AND :new.b_id = '单号';
end;
/
(
A_ID VARCHAR2(10) CONSTRAINT PK_A_ID PRIMARY KEY,
FLD1 INT,
FLD2 INT,
FLD3 INT
);
CREATE TABLE B
(
B_ID VARCHAR2(10) CONSTRAINT PK_B_ID PRIMARY KEY,
A_ID VARCHAR2(10) CONSTRAINT FK_A_ID REFERENCES A(A_ID),
FLD1 INT,
FLD2 INT,
FLD3 INT,
FLD4 INT
);
在ORACLE中有没有像SQL SERVER这样的更新语句的语法?
UPDATE A
SET
FLD1 = B.FLD1,
FLD2 = B.FLD2,
FLD3 = B.FLD3 + B.FLD4
FROM B
WHERE A.A_ID = B.A_ID AND B.B_ID = '单号'
好象在ORACLE中的DELETE和UPDATE语句不支持FROM子句。
UPDATE A SET A.name = (SELECT B.name FROM B WHERE B.a_id = A.a_id AND B.b_id = '单号')
WHERE EXISTS (SELECT 1 FROM B WHERE B.a_id = A.a_id AND B.b_id = '单号')
ORACLE不至于是这样的吧?!
痛苦啊!!!!!!!!
UPDATE A
SET
FLD1 = B.FLD1,
FLD2 = B.FLD2,
FLD3 = B.FLD3 + B.FLD4
FROM B
WHERE A.A_ID = B.A_ID AND B.B_ID = '单号'ORACLE:
UPDATE A
SET
FLD1 = (SELECT FLD1 FROM B WHERE A.A_ID = B.A_ID AND B.B_ID = '单号'),
FLD2 = (SELECT FLD2 FROM B WHERE A.A_ID = B.A_ID AND B.B_ID = '单号'),
FLD3 = (SELECT FLD3 FROM B WHERE A.A_ID = B.A_ID AND B.B_ID = '单号')
WHERE EXISTS(SELECT 1 FROM B WHERE A.A_ID = B.A_ID AND B.B_ID = '单号')
ORACLE的这种语法恐怖啊!
不得不重复对条件进行判断了!
我想ORACLE为什么这么简单的更新语句会搞得如此复杂,希望高手能说说ORACLE这么做的原因。
SELECT
A.FLD1,
B.FLD2
FROM A, B
WHERE
)
SET A.FLD1 = B.FLD2;
http://expert.csdn.net/Expert/topic/1712/1712439.xml?temp=.392193
是看了搂主的贴子有感而发的
如果不需要定期实时更新,直接用UPDATE语句,
以上说使用触发器者是没有实际开发经验的吧,这样的语句写触发器实在不可行啊!!!!
SET A.name = (
SELECT B.NAME
FROM B
WHERE A.a_id = B.a_id AND B.b_id = '单号');
where A.a_id in (select b_id from B where b_id = '单号')
太慢.根本就是难以忍受.各位如有最好的解决方法或想讨论的,please email to me : [email protected]