OraFAQ Forum: SQL & PL/SQL » INSERT INTO with subquery select from same table = ORA-00947 not enough values

Home » SQL & PL/SQL » SQL & PL/SQL » INSERT INTO with subquery select from same table = ORA-00947 not enough values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

INSERT INTO with subquery select from same table = ORA-00947 not enough values [message #43067]

Fri, 06 June 2003 06:11

Ed Randall
Messages: 3
Registered: June 2003

Junior Member

Using 9i I am trying to copy a set of rows from a table whilst making minor alterations as follows:

INSERT INTO SEC_USERFUNCTION (USER_ID, FUNCTION_ID)
VALUES ((SELECT 3 AS USER_ID, FUNCTION_ID from SEC_USERFUNCTION where USER_ID=2));

The table has only 2 columns USER_ID and FUNCTION_ID, both NUMBER; Running the subquery on its own returns 2 columns, yet when I try to do the insert I get ORA-00947 not enough values, please help! Why is this?

Thanks

Ed

Report message to a moderator

Re: INSERT INTO with subquery select from same table = ORA-00947 not enough values [message #43068 is a reply to message #43067]

Fri, 06 June 2003 06:36

Martin Chadderton
Messages: 35
Registered: May 2003

Member

It's simply because you have a select in the values clause. Oracle interprets/expects a select like this to return a single value. I suspect what you need is to lose the VALUES clause, i.e.

SQL> CREATE TABLE t ( a VARCHAR2(10), b VARCHAR2(10) );

Table created.

SQL> INSERT INTO t
2 VALUES
3 (
4 ( SELECT 'x', 'y' FROM dual )
5 );
INSERT INTO t
*
ERROR at line 1:
ORA-00947: not enough values

SQL> INSERT INTO t
2 SELECT 'x', 'y' FROM dual;

1 row created.

Regards

Report message to a moderator

Re: INSERT INTO with subquery select from same table = ORA-00947 not enough values [message #43069 is a reply to message #43067]

Fri, 06 June 2003 06:42

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Loose the "values" keyword, instead use:

INSERT INTO sec_userfunction(user_id, function)
SELECT 3 AS user_id
     , function_id 
  FROM sec_userfunction
 WHERE user_id=2;

What happens here is that Oracle sees your select as a single-row subquery as a replacement for a hard coded value. In fact, Oracle is seeing a Select to fill your user_id at the insert, but it sees no value for function_id. This is causing the insert to fail.

MHE

Report message to a moderator

Re: INSERT INTO with subquery select from same table = ORA-00947 not enough values [message #43295 is a reply to message #43067]

Wed, 02 July 2003 14:33

Zorden
Messages: 2
Registered: July 2003

Junior Member

And what if i want to mix selected values and 'direct' values. My Problem is, that i have 5 coloums (id, val1, val2, status, chtime). i'd like summarize val1 and val2 group by id and to store these values in the same database with a new single id, and i'd like to set the status to 'done' for this single row...

can i do it with only one insert/select?

Z.

Report message to a moderator

QUESTION: INSERT INTO with subquery select from same table = ORA-00947 not enough values [message #43296 is a reply to message #43067]

Wed, 02 July 2003 14:35

Zorden
Messages: 2
Registered: July 2003

Junior Member

Urgent help needed...

And what if i want to mix selected values and 'direct' values. My Problem is, that i have 5 coloums (id, val1, val2, status, chtime). i'd like summarize val1 and val2 group by id and to store these values in the same database with a new single id, and i'd like to set the status to 'done' for this single row...

can i do it with only one insert/select?

Z.

Report message to a moderator

Re: INSERT INTO with subquery select from same table = ORA-00947 not enough values [message #43439 is a reply to message #43295]

Sun, 20 July 2003 02:09

Ed Randall
Messages: 3
Registered: June 2003

Junior Member

INSERT INTO table2 (id, val1, val2, status, chtime)
(SELECT id, val1, val2, 'done', chtime
FROM table1 WHERE ...)

Report message to a moderator

Previous Topic:	about with clause
Next Topic:	Problem in retrieving voucher no. pls. help ...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 11 06:17:47 CDT 2025