ORA-00913: too many values分析

最新推荐文章于 2023-09-18 12:35:44 发布

servaywong

最新推荐文章于 2023-09-18 12:35:44 发布

阅读量2w

点赞数

CC 4.0 BY-SA版权

分类专栏：技术洞察(TechInsight) 文章标签： microsoft subquery insert sql oracle

本文链接：https://blog.youkuaiyun.com/servaywong/article/details/5193178

技术洞察(TechInsight) 专栏收录该内容

2 篇文章

订阅专栏

本文解析了Oracle数据库中ORA-00913:too many values错误的原因及解决办法，主要通过调整INSERT语句中VALUES子句与列数匹配来解决，并提供了示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天晚上旁边的同事在调试代码时出现了下面的bug：

ORA-00913: too many values

。。。。（后面的具体的就不写了）

第一次碰到这样的情况，上网一查才知道：原来同事的sql语句写错了：具体就不说了，主要是一个INSERT语句：结果

VALUES后面的插入的数据比前面的字段名多了一个，所以才出现了所谓的“too many values”错误。

---------------------------------------

我参考的1个帖子如下：感觉写得不错（虽然是e文写的）：

http://www.techonthenet.com/oracle/errors/ora00913.php

Oracle/PLSQL: ORA-00913 Error

Error:

ORA-00913: too many values

Cause:

You tried to execute an SQL statement that required two sets of equal values, but you entered more items in the second set than was in the first set.

Action:

The options to resolve this Oracle error are:

This error often occurs when you are performing an INSERT statement and enter more values in the VALUES clause than the number of columns that you listed.

For example, if you executed the following INSERT statement:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(1000, 'Microsoft', 'Bill Gates');

In this example, you've chosen to insert values into 2 columns (supplier_id and supplier_name), but you've entered 3 values (1000, Microsoft, and Bill Gates).

You need to modify your INSERT statement so there are the same number of columns as there are values. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(1000, 'Microsoft');

This error can also occur when your subquery in the WHERE clause returns too many columns.

For example, if you executed the following SQL statement:

SELECT * FROM suppliers
WHERE supplier_id > 5000
AND supplier_id IN (SELECT * FROM products
WHERE product_name LIKE 'H%);

In this example, the subquery returns all columns from the products table. You need to modify the subquery to return only one column as follows:

SELECT * FROM suppliers
WHERE supplier_id > 5000
AND supplier_id IN (SELECT product_id FROM products

WHERE product_name LIKE 'H%);