INSERT SELECT statement in Oracle 11G
I'm trying to run a very simple sql statement in Oracle 11g.
insert into table1 (col1, col2) values (select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2);
Very simple query. Cartesian join old table 1 to old table 2, put the resulting values into table 1.
I've run the subquery by itself, and it works perfectly.
select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2
When I try to run the full statement, I get the following error:
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
I can't get it to work in MySql either. Something is wrong with my statement, but I'm not sure what it is.
Your query should be:
insert into table1 (col1, col2)
select t1.col1, t2.col2
from oldtable1 t1, oldtable2 t2
Ie without the VALUES part.
Get rid of the values keyword and the parens. You can see an example here.
使用'select'作为源时，不需要'values'子句。
insert into table1 (col1, col2)
select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2;