Menu
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.
461
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
The INSERT statement adds one or more new rows of data to a database table. For a full description of the INSERT statement, see Oracle Database SQL Reference. Syntax.
The Oracle INSERT INTO SELECT statement requires the data type of the source and target tables match. If you want to copy all rows from the source table to the target table, you remove the WHERE clause. Otherwise, you can specify which rows from the source table should be copied to the target table.
Your query should be:
insert into table1 (col1, col2) select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2 I.e. without the VALUES part.
126
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
