| INSERT with subquery in VALUES [message #628313] |
Sun, 23 November 2014 20:58  |
aliyesami
Messages: 28
Registered: October 2005
Location: Florida
|
Junior Member |
|
|
i am reading everywhere that subquery cant be used in the value clause of an insert statement . I did some tests and if i use just one column than the subquery succeeds in value clause but for more than one column i am getting error , please zee below . Can someone explain this behaviour ?
thanks
SQL> CREATE TABLE NEW_TEST AS SELECT * FROM TEST;
Table created.
SQL> INSERT INTO TEST VALUES (1,3);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO NEW_TEST (ID,VALUE) VALUES((SELECT ID,VALUE FROM TEST WHERE ID>
1));
INSERT INTO NEW_TEST (ID,VALUE) VALUES((SELECT ID,VALUE FROM TEST WHERE ID>1))
*
ERROR at line 1:
ORA-00947: not enough values
SQL>
SQL> INSERT INTO NEW_TEST (ID) VALUES((SELECT ID FROM TEST WHERE ID>1));
1 row created.
SQL> DESC TEST
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
VALUE NUMBER(38)
SQL> DESC NEW_TEST
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
VALUE NUMBER(38)
SQL>
Edited by Lalit : Added code tags
[Updated on: Mon, 24 November 2014 01:18] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: INSERT with subquery in VALUES [message #628319 is a reply to message #628313] |
Mon, 24 November 2014 01:18  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
aliyesami wrote on Mon, 24 November 2014 08:28
SQL> INSERT INTO NEW_TEST (ID,VALUE) VALUES((SELECT ID,VALUE FROM TEST WHERE ID>
1));
INSERT INTO NEW_TEST (ID,VALUE) VALUES((SELECT ID,VALUE FROM TEST WHERE ID>1))
*
ERROR at line 1:
ORA-00947: not enough values
You cannot do that using VALUES clause.
SQL> drop table emp_new purge;
Table dropped.
SQL> create table emp_new as select * from emp where 1=2;
Table created.
SQL> insert into emp_new select * from emp;
14 rows created.
SQL> select count(*) from emp_new;
COUNT(*)
----------
14
SQL> insert into emp_new(empno, ename) select empno, ename from emp;
14 rows created.
SQL> select count(*) from emp_new;
COUNT(*)
----------
28
SQL>
And please use code tags. Read http://www.orafaq.com/forum/t/174502/
|
|
|
|
|
|
| Re: INSERT with subquery in VALUES [message #628325 is a reply to message #628320] |
Mon, 24 November 2014 02:39 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 24 November 2014 12:59
Quote:You cannot do that using VALUES clause.
And what about what flyboy said?
Sorry, I don't understand your question.
I completely agree with Flyboy, he said, Quote:it has the limits you observed: one column and at most one value returned .
And I showed how it could be done for multiple columns, i.e. not using VALUES clause.
|
|
|
|
|
|
| Re: INSERT with subquery in VALUES [message #628363 is a reply to message #628317] |
Mon, 24 November 2014 09:01 |
aliyesami
Messages: 28
Registered: October 2005
Location: Florida
|
Junior Member |
|
|
flyboy wrote on Mon, 24 November 2014 00:22
By the way, how did you find out the necessity of extra parenthesis? Seems that that source is not part of "everywhere".
That was my second question I was planning to ask, why the following fails ? I found out the right syntax by just playing around with the syntax and guessing.
SQL> INSERT INTO NEW_TEST (ID) VALUES(SELECT ID FROM TEST WHERE ID>1);
INSERT INTO NEW_TEST (ID) VALUES(SELECT ID FROM TEST WHERE ID>1)
*
ERROR at line 1:
ORA-00936: missing expression
thanks for your help
|
|
|
|
|
|
| Re: INSERT with subquery in VALUES [message #628372 is a reply to message #628363] |
Mon, 24 November 2014 09:38 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Thank you for feedback, hope you read about it in the link I posted.
Just for clearer demonstration of scalar subquery expression, these statements are also correct:
INSERT INTO NEW_TEST (ID,VALUE)
VALUES(1, (SELECT VALUE FROM TEST WHERE ID>1));
INSERT INTO NEW_TEST (ID,VALUE)
VALUES((SELECT ID FROM TEST WHERE ID>1), 3); |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
The braces are critical in this case. Values clause expects a single value for one column inside braces, however, the subquery result should be resolved before to provide the value, and it needs to be inside braces. Else, the values clause thinks the subquery itself to be the value before it is executed.
