Query

How to Resolve ORA-00913: too many values

ORA-00913

Let’s see a SQL statement containing 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

Column List Mismatch

The error message “too many values” actually means “too many columns” if I may say so. Thus you have to make the subquery return exact column list to match the outer statement. In this case, only one 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);

ORA-00913 When INSERT

Another type of column list mismatch may occur at INSERT SELECT statement.

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

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 *