insert into ac12(id,aab001,aac111,aac001,aac124,aac125) values((select SEQ_AC12_ID.nextval as "id",ac.aab001 as "aab001", 9 as "aac111", ac.aac001 as "aac001", '2010-05-05' as "aac124",'2010-10-10' as "aac125" from ac01 ac where ac.aac008 = 1 and ac.aac001 not in (select ac1.aac001 from ac12 ac1 where ac1.aac111 = 9)));结果:ORA-00947: 没有足够的值 (select SEQ_AC12_ID.nextval as "id",ac.aab001 as "aab001", 9 as "aac111", ac.aac001 as "aac001", '2010-05-05' as "aac124",'2010-10-10' as "aac125" from ac01 ac where ac.aac008 = 1 and ac.aac001 not in (select ac1.aac001 from ac12 ac1 where ac1.aac111 = 9))结果: id aab001 aac111 aac001 aac124 aac125
---------- -------- ---------- ---------- ---------- ----------
137 00000001 9 0000004764 2010-05-05 2010-10-10
138 00000001 9 0000004766 2010-05-05 2010-10-10
139 00000001 9 0000004805 2010-05-05 2010-10-10
140 00000001 9 0000011488 2010-05-05 2010-10-10
141 00000001 9 0000011489 2010-05-05 2010-10-10
142 00000001 9 0000011490 2010-05-05 2010-10-10
143 00000001 9 0000011491 2010-05-05 2010-10-10
144 00000001 9 0000011492 2010-05-05 2010-10-10
145 00000001 9 0000011493 2010-05-05 2010-10-10
146 00000001 9 0000011494 2010-05-05 2010-10-10
147 00000001 9 0000011495 2010-05-05 2010-10-10
148 00000001 9 0000011486 2010-05-05 2010-10-10
149 00000001 9 0000011487 2010-05-05 2010-10-10
150 00000001 9 0000011463 2010-05-05 2010-10-10
151 00000001 9 0000011464 2010-05-05 2010-10-10
152 00000001 9 0000011465 2010-05-05 2010-10-1016 rows selected.
这是为什么呢? 列数都是一样的啊
解决方案 »
- RMAN 脚本学习
- ORACLE服务未启动时,无法通过sqlplus启动服务
- windows server 2008 下安装Oracle10g的问题
- oracle出错
- 数据库里明明有数据,为何查询出来没数据?
- 请教大家一个oracle 9i的oracle management server创建问题
- 安装文件和数据文件都在,重装系统后如何恢复oracle11gr2??
- 新手提问:关于存储过程
- 什么是并行度?其概念是什么?
- 把ACCESS数据表中的数据导入ORACLE数据库中,导致中文字段的数据成为乱码,怎么办?
- audit drop any table 审计
- 这个SQL在SQLSver2000中查询相当快,可是在oracle中超级慢,应该如何写
insert into ac12(id,aab001,aac111,aac001,aac124,aac125) values(
(select SEQ_AC12_ID.nextval as "id",ac.aab001 as "aab001", 9 as "aac111", ac.aac001 as "aac001", '2010-05-05' as "aac124",'2010-10-10' as "aac125" from ac01 ac where ac.aac008 = 1 and ac.aac001 not in (select ac1.aac001 from ac12 ac1 where ac1.aac111 = 9)));
-- Oracle 不支持这种写法,需要先将查询的结果插入到变量里,在把变量写道values里。
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977(满)
DBA3 群:63306533; 聊天 群:40132017
最好用存储过程. 不用存储过程,就多弄几张表,将查询的结果简单化。 最后可用类似于以下的方法插入:
insert into table as select * from table.
insert into table select * from table.没有as
前提是你ac12只有(id,aab001,aac111,aac001,aac124,aac125)这六个字段
insert into ac12 select SEQ_AC12_ID.nextval as "id",ac.aab001 as "aab001", 9 as "aac111", ac.aac001 as "aac001", '2010-05-05' as "aac124",'2010-10-10' as "aac125" from ac01 ac where ac.aac008 = 1 and ac.aac001 not in (select ac1.aac001 from ac12 ac1 where ac1.aac111 = 9)
insert into ac12(id,aab001,aac111,aac001,aac124,aac125)
select SEQ_AC12_ID.nextval as "id",ac.aab001 as "aab001", 9 as "aac111", ac.aac001 as "aac001", '2010-05-05' as "aac124",'2010-10-10' as "aac125" from ac01 ac where ac.aac008 = 1 and ac.aac001 not in (select ac1.aac001 from ac12 ac1 where ac1.aac111 = 9);后面SELECT中别名可以不要
你把所有的别名去掉看看(应该没关系的)
难道是aac124字段类型是日期的?如果是,需要增加函数转换 TO_DATE('2010-05-05','YYYY-MM-DD')
insert into ac12(id,aab001,aac111,aac001,aac124,aac125)
select SEQ_AC12_ID.nextval ,ac.aab001 , 9 , ac.aac001 , '2010-05-05' ,'2010-10-10'
from ac01 ac where ac.aac008 = 1 and ac.aac001 not in (select ac1.aac001 from ac12 ac1 where ac1.aac111 = 9);
INSERT INTO ac12(id,aab001,aac111,aac001,aac124,aac125) SELECTSEQ_AC12_ID.nextval as "id",
ac.aab001 as "aab001",
9 as "aac111",
ac.aac001 as "aac001",
'2010-05-05' as "aac124",
'2010-10-10' as "aac125" FROM ac01 ac
WHERE
ac.aac008 = 1 and
ac.aac001 NOT IN (SELECT ac1.aac001 FROM ac12 ac1 WHERE ac1.aac111 = 9)
(ID, aab001, aac111, aac001, aac124, aac125)
SELECT seq_ac12_id.NEXTVAL AS ID, ac.aab001 AS aab001, 9 AS aac111,
ac.aac001 AS aac001, TO_DATE ('2010-08-08', 'YYYY-MM-DD') AS aac124,
TO_DATE ('2010-08-25', 'YYYY-MM-DD') AS aac125
FROM ac01 ac
WHERE c.aac008 = 1
AND NOT EXISTS (SELECT *
FROM ac12 ac1
WHERE ac1.aac001 = ac.aac001 AND ac1.aac111 = '9')列类型需要转型