当前位置: 代码迷 >> Oracle开发 >> 去反复报ORA-00913: too many values 。
  详细解决方案

去反复报ORA-00913: too many values 。

热度:231   发布时间:2016-04-24 08:02:24.0
去重复报ORA-00913: too many values 。。。

--DELETE PRICE_INFO_TBL  

select * from PRICE_INFO_TBL 
WHERE (PROD_SID,PRICE_BOOK_SID,CREATOR) IN 
(
select PROD_SID,PRICE_BOOK_SID,CREATOR,count(*) from PRICE_INFO_TBL group by PROD_SID,PRICE_BOOK_SID,PROJECT_SID,CREATOR having count(*) >1
and PROJECT_SID = 10 and PRICE_BOOK_SID = 33 and CREATOR LIKE '0005546904%'
) AND ROWID NOT IN (
SELECT MIN(ROWID) FROM PRICE_INFO_TBL GROUP BY PROD_SID,PRICE_BOOK_SID,PROJECT_SID,CREATOR HAVING COUNT(*) > 1 and PROJECT_SID = 10
and PRICE_BOOK_SID = 33 and CREATOR LIKE '0005546904%'


请问为什么会报
 ORA-00913: too many values 错误呢

我想删除 表 PRICE_INFO_TBL 

PRICE_BOOK_SID = 33 CREATOR 和LIKE '0005546904%'的 筛选结果 并且 


PROD_SID,PRICE_BOOK_SID,CREATOR 这三个字段重复的记录  



谢谢!@

------解决方案--------------------
下面两处红色部分,也就是in的列数要对应,一样多,可以把count(*)去掉
select * from PRICE_INFO_TBL
WHERE (PROD_SID,PRICE_BOOK_SID,CREATOR) IN
(
select PROD_SID,PRICE_BOOK_SID,CREATOR,count(*) from PRICE_INFO_TBL group by PROD_SID,PRICE_BOOK_SID,PROJECT_SID,CREATOR having count(*) >1
and PROJECT_SID = 10 and PRICE_BOOK_SID = 33 and CREATOR LIKE '0005546904%'
) AND ROWID NOT IN (
SELECT MIN(ROWID) FROM PRICE_INFO_TBL GROUP BY PROD_SID,PRICE_BOOK_SID,PROJECT_SID,CREATOR HAVING COUNT(*) > 1 and PROJECT_SID = 10
and PRICE_BOOK_SID = 33 and CREATOR LIKE '0005546904%'
)

------解决方案--------------------

--DELETE PRICE_INFO_TBL

select * from PRICE_INFO_TBL a where rowid not in (select min(ROWID) from PRICE_INFO_TBL b where a.PROD_SID = b.PROD_SID and a.PRICE_BOOK_SID=b.PRICE_BOOK_SID and a.CREATOR = b.CREATOR) and 
 a.PRICE_BOOK_SID = 33 and a.CREATOR LIKE '0005546904%'
  相关解决方案