Query

How to Resolve ORA-00913: too many values

ORA-00913: too many values

ORA-00913 When SELECT

Let’s see a SQL statement containing a subquery that can reproduce ORA-00913 for an example.

SQL> select * from employees where department_id in (select department_id, manager_id from departments where location_id = 1700);
select * from employees where department_id in (select department_id, manager_id from departments where location_id = 1700)
                                                *
ERROR at line 1:
ORA-00913: too many values

Cause: Column List Mismatch

The error message “too many values” actually means “too many column values” if I may say so. That is to say, the number of returned columns in the inner query is more than required number of columns in the outer statement.

Solution

Therefore, you have to make the subquery return exact column list to match the outer statement. In this case, only one column “department_id” should be in the column list of subquery.

SQL> select * from employees where department_id in (select department_id from departments where location_id = 1700);

In contrast, ORA-00947: not enough values means the number of returned columns in the inner query is less than required number of columns in the outer statement.

By the way, ORA-00913 or ORA-00947 complains about returned number of columns, not rows. For accommodating multiple rows, I use IN instead of = (equal) operator in case of error ORA-01427 in the above statement.

ORA-00913 When INSERT

Another type of column list mismatch may occur at INSERT … SELECT statement. Let’s see how I reproduce ORA-00913.

First of all, I create an empty table EMPLOYEES_2 from EMPLOYEES.

SQL> create table employees_2 as select * from employees where 1 = 2;

Table created.

Then I drop one column from EMPLOYEES_2.

SQL> alter table employees_2 drop column PHONE_NUMBER;

Table altered.

Now I insert some rows by INSERT … SELECT.

SQL> insert into employees_2 select * from employees;
insert into employees_2 select * from employees
            *
ERROR at line 1:
ORA-00913: too many values

Once again, it’s a column list mismatch problem, although you don’t see any column in the statement. I know it’s very confusing. Just remember that “too many values” means “too many columns”.

Solution

The solution is to compare two tables’ definitions and then adjust the column list to match each other.

Leave a Reply

Your email address will not be published. Required fields are marked *