ORA-00913 too many values select statement Common Causes and Solutions

The Oracle error ORA-00913: too many values occurs when the number of values provided in a SELECT or INSERT statement doesn't match the expected number of columns. This typically happens when you're trying to insert or select multiple values where only a single value is expected.

Common Causes and Solutions

Incorrect INSERT INTO Syntax: You may be trying to insert multiple rows of data using a VALUES clause, but the number of values doesn't match the number of columns in the table.

Example Problem:

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (101, 'John', 'Doe', 50000);

In this case, you're inserting four values into a table expecting only three columns.

Solution: 

Ensure the number of values matches the number of columns in the INSERT statement.

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (101, 'John', 'Doe');

 

Incorrect SELECT with Subquery: You may be using a subquery that returns more than one column when only one column is expected.

Example Problem:

SELECT department_id, (SELECT department_id, department_name FROM departments)
FROM employees;

Here, the subquery returns two columns, but it's being treated as if it returns only one.

Solution: 

Ensure that the subquery returns a single value or adjust the query structure.

SELECT department_id, (SELECT department_name FROM departments WHERE department_id = employees.department_id)FROM employees;

Using INSERT INTO ... SELECT with Column Mismatch: If you're using INSERT INTO ... SELECT, the number of columns in the INSERT must match the number of columns in the SELECT.

Example Problem:

INSERT INTO employees (employee_id, first_name)
SELECT employee_id, first_name, last_name FROM temp_employees;

The INSERT INTO expects two columns, but the SELECT statement returns three.

Solution: 

Adjust the SELECT statement to match the number of columns.

INSERT INTO employees (employee_id, first_name)
SELECT employee_id, first_name FROM temp_employees;

Using IN with a Subquery Returning Multiple Columns: You may be using the IN clause with a subquery that returns multiple columns instead of one.

Example Problem:

SELECT * FROM employees
WHERE (employee_id, department_id) IN (SELECT employee_id, department_id FROM temp_employees);

The IN clause expects a single column.

Solution: 

Use a JOIN instead if you need to compare multiple columns:

SELECT e.*
FROM employees e
JOIN temp_employees t ON e.employee_id = t.employee_id AND e.department_id = t.department_id;

Commands to Troubleshoot

Check Table Structure: Use the DESCRIBE command to check how many columns a table has.

DESCRIBE employees;

Test Subquery Output: Run the subquery independently to check how many columns it returns.

SELECT department_id, department_name FROM departments;

By matching the number of values and columns, you can resolve the ORA-00913: too many values error.