simple insert statement using select and values [message #299163] |
Sun, 10 February 2008 04:54  |
cygnusx04
Messages: 16 Registered: February 2008
|
Junior Member |
|
|
this is so simple but it's killing me.create table checker (rec_no INTEGER, table_name VARCHAR2(8));
insert into checker (select rec_no from xxxx where validated='X','CORRESP');
*
ERROR at line 1:
ORA-00907: missing right parenthesis
i'm just trying to insert a stack of rec_no's and the second column will always have CORRESP as the table_name
what am i doing wrong? this seems so simple.
thanks
[EDITED by LF: added [code] tags]
[Updated on: Sun, 10 February 2008 09:29] by Moderator Report message to a moderator
|
|
|
|
Re: simple insert statement using select and values [message #299169 is a reply to message #299166] |
Sun, 10 February 2008 05:28   |
cygnusx04
Messages: 16 Registered: February 2008
|
Junior Member |
|
|
hope this is better.
Oracle 10.2.0.1.0 (windows 2003 SP2)
SQL> create table checker (rec_no INTEGER, table_name varchar2(8));
Table created.
SQL> insert into checker (select rec_no from xxxxx where validated='X
','CORRESP');
insert into checker (select rec_no from xxxxx where validated='X','CO
RRESP')
*
ERROR at line 1:
ORA-00907: missing right parenthesis
[Updated on: Sun, 10 February 2008 05:30] Report message to a moderator
|
|
|
|
Re: simple insert statement using select and values [message #299171 is a reply to message #299170] |
Sun, 10 February 2008 05:51   |
cygnusx04
Messages: 16 Registered: February 2008
|
Junior Member |
|
|
SQL> select rec_no from xxxxx where validated='X' and rownum<5;
6771
6765
6773
6770
SQL> desc xxxxx
Name Null? Type
----------------------------------------- -------- ----------------
REC_NO NOT NULL NUMBER(38)
everything looks good to me, I think my syntax is wrong - just don't know what it should be.
SQL> create table checker (rec_no NUMBER);
Table created.
SQL> insert into checker (select rec_no from xxxxx where validated='X');
10 rows created.
SQL>
so it appears it's the 'CORRESP' that is causing the problem.
|
|
|
|
Re: simple insert statement using select and values [message #299173 is a reply to message #299163] |
Sun, 10 February 2008 05:59   |
cygnusx04
Messages: 16 Registered: February 2008
|
Junior Member |
|
|
eh!? I haven't figured it out! If i create the table with one field, yes it works. but i still can't get it to work if i have both REC_NO and TABLE_NAME.
the insert statement is incorrect. I need help with the correct syntax!!!!!! Should i be posting in the advanced section instead?
SQL> create table checker (rec_no INTEGER, table_name varchar2(8));
Table created.
SQL> insert into checker (select rec_no from xxxxx where validated='X
','CORRESP');
insert into checker (select rec_no from xxxxx where validated='X','CO
RRESP')
*
ERROR at line 1:
ORA-00907: missing right parenthesis
|
|
|
|
|
|
|
Re: simple insert statement using select and values [message #299188 is a reply to message #299176] |
Sun, 10 February 2008 09:20   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
cygnusx04 wrote on Sun, 10 February 2008 13:15 | my table checker has 2 fields
REC_NO
TABLE_NAME
REC_NO will be the value from select
and TABLE_NAME will be 'CORRESP'
i thought the comma seperated the fields??
|
Back to the basic: forget about the insert for a while.
How do you select two columns?
How do you select a column plus a constant ('CORRESP')
When you have the select, merge it back into the insert
|
|
|
|
|
Re: simple insert statement using select and values [message #299214 is a reply to message #299212] |
Sun, 10 February 2008 12:35  |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Oh? Reading your initial post, I thought that the solution was more like
insert into checker (rec_no)
select rec_no
from xxxx
where validated IN ('X', 'CORRESP');
Besides, I'd suggest you to ALWAYS name full column list in the INSERT statement. It means that INSERT INTO checker
SELECT ... isn't as good asINSERT INTO checker (rec_no)
SELECT ... Why? Because you can never tell what will happen in the future. You might, for example, recreate the table using different column order and - if datatypes differ - statement will fail.
|
|
|