Home » SQL & PL/SQL » SQL & PL/SQL » simple insert statement using select and values
simple insert statement using select and values [message #299163] Sun, 10 February 2008 04:54 Go to next message
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 #299166 is a reply to message #299163] Sun, 10 February 2008 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something is wrong in "validated='X','CORRESP'".

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: simple insert statement using select and values [message #299169 is a reply to message #299166] Sun, 10 February 2008 05:28 Go to previous messageGo to next message
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 #299170 is a reply to message #299169] Sun, 10 February 2008 05:36 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Still your SELECT statement doesn't look Right.

Execute the SELECT statement first separately and see if it getting you the right return that you need to Insert into the other table.

Goodluck,
Baz

Re: simple insert statement using select and values [message #299171 is a reply to message #299170] Sun, 10 February 2008 05:51 Go to previous messageGo to next message
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 #299172 is a reply to message #299171] Sun, 10 February 2008 05:54 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Good, you figured out your problem by yourself.

Keep it up.

Cheers
Re: simple insert statement using select and values [message #299173 is a reply to message #299163] Sun, 10 February 2008 05:59 Go to previous messageGo to next message
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 #299174 is a reply to message #299173] Sun, 10 February 2008 06:02 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Not really. You are posting in the right Forum.

You have the Answer, you are using a wrong SELECT statement again.

I am sure you will find the solution.
Re: simple insert statement using select and values [message #299175 is a reply to message #299173] Sun, 10 February 2008 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain what is the meaning of "validated='X','CORRESP'".

Regards
Michel

Re: simple insert statement using select and values [message #299176 is a reply to message #299163] Sun, 10 February 2008 06:15 Go to previous messageGo to next message
cygnusx04
Messages: 16
Registered: February 2008
Junior Member
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??
Re: simple insert statement using select and values [message #299177 is a reply to message #299176] Sun, 10 February 2008 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no TABLE_NAME in your expression "validated='X','CORRESP'"
Why do you "validated=" and not "table_name="?

Quote:
i thought the comma seperated the fields??

There is nothing to think or guess in SQL, you just have to follow the SQL syntax.
SQL Reference

Regards
Michel

[Updated on: Sun, 10 February 2008 06:29]

Report message to a moderator

Re: simple insert statement using select and values [message #299188 is a reply to message #299176] Sun, 10 February 2008 09:20 Go to previous messageGo to next message
Frank
Messages: 7880
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 #299190 is a reply to message #299188] Sun, 10 February 2008 09:31 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A hint: check use of the IN operator in the WHERE clause.
Re: simple insert statement using select and values [message #299212 is a reply to message #299163] Sun, 10 February 2008 11:56 Go to previous messageGo to next message
cygnusx04
Messages: 16
Registered: February 2008
Junior Member
ok finally got it.

insert into checker (select rec_no,'CORRESP' from xxxxx where validated ='X');


It was too easy in the end!!
Re: simple insert statement using select and values [message #299214 is a reply to message #299212] Sun, 10 February 2008 12:35 Go to previous message
Littlefoot
Messages: 20899
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 as
INSERT 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.
Previous Topic: set sqlplus from bash shell
Next Topic: Performance analysis of package
Goto Forum:
  


Current Time: Wed Dec 07 14:19:35 CST 2016

Total time taken to generate the page: 0.16020 seconds