How to Resolve 'ORA-00936: missing expression' in Oracle 11g INSERT SELECT Statements?

Troubleshooting an "ORA-00936" Error with an INSERT SELECT Statement in Oracle 11G

An "ORA-00936: missing expression" error can arise when executing an INSERT SELECT statement in Oracle 11G. Let's analyze a specific case to resolve this issue.

Problem:

A SQL statement attempts to insert data from a subquery into a table:

insert into table1 (col1, col2) values (select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2);

The subquery, when executed separately, operates correctly, retrieving rows from two tables, oldtable1 and oldtable2. However, when executed as the data source for the INSERT statement, the "ORA-00936: missing expression" error is encountered.

Solution:

The error stems from an incorrect syntax in the INSERT statement. The VALUES keyword is not necessary when utilizing a SELECT statement to populate the target table. The correct syntax should be:

insert into table1 (col1, col2)
select t1.col1, t2.col2 
from oldtable1 t1, oldtable2 t2

By omitting the VALUES portion, the INSERT statement correctly executes the subquery and populates table1 with the selected data.

The above is the detailed content of How to Resolve 'ORA-00936: missing expression' in Oracle 11g INSERT SELECT Statements?. For more information, please follow other related articles on the PHP Chinese website!