Home » SQL & PL/SQL » SQL & PL/SQL » SELECT within INSERT
SELECT within INSERT [message #210934] Sat, 23 December 2006 13:19 Go to next message
avion
Messages: 11
Registered: December 2006
Junior Member
Hi,

I've entered some data with a SELECT within an INSERT, but when I select the table it shows nothing in the column where i specified the select.

create table  DUMMYTABLE1
(
id1 int not null,
testvalues int,
primary key (id1));


create table DUMMYTABLE2
(
id2 int not null,
id1 int not null,
testvalues int,
foreign key (id1) references DUMMYTABLE1(id1) on delete cascade,
primary key (id1,id2));


insert into DUMMYTABLE1 
(id1,testvalues) values('4',
(select DUMMYTABLE2.days from DUMMYTABLE2,DUMMYTABLE1 
where DUMMYTABLE1.id1=DUMMYTABLE2.id1));


insert into DUMMYTABLE1 
(id1,testvalues) values('2',
(select DUMMYTABLE2.days from DUMMYTABLE2,DUMMYTABLE1 
where DUMMYTABLE1.id1=DUMMYTABLE2.id1));


insert into DUMMYTABLE1 
(id1,testvalues) values('3',
(select DUMMYTABLE2.days from
DUMMYTABLE2,DUMMYTABLE1 
where DUMMYTABLE1.id1=DUMMYTABLE2.id1));


insert into DUMMYTABLE2 (id2,id1,testvalues) values ('1','4','6');
insert into DUMMYTABLE2 (id2,id1,testvalues) values ('2','4','5');
insert into DUMMYTABLE2 (id2,id1,testvalues) values ('3','2','8');
insert into DUMMYTABLE2 (id2,id1,testvalues) values ('3','3','7');
commit;


Result:

SQL> SELECT * FROM DUMMYTABLE1;

      ID1      TESTVALUES
---------- ----------
         4
         2
         3


Any ideas?

Thanks Smile

[Updated on: Sat, 23 December 2006 13:20]

Report message to a moderator

Re: SELECT within INSERT [message #210940 is a reply to message #210934] Sat, 23 December 2006 22:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A pretty good idea...

You are populating DUMMYTABLE1 at a time when DUMMYTABLE2 is empty. The script you use to insert a row into DUMMYTABLE1 populates TESTVALUES from DUMMYTABLE2 which is - um... empty. What value other than NULL were you hoping to get from an empty table?

Ross Leishman
Re: SELECT within INSERT [message #210965 is a reply to message #210940] Sun, 24 December 2006 06:26 Go to previous messageGo to next message
avion
Messages: 11
Registered: December 2006
Junior Member
Thanks for the reply.

Hmm I thought it would dynamically select the rows every time I run the SELECT * query..

Isn't there a way to do it?
Re: SELECT within INSERT [message #210976 is a reply to message #210965] Sun, 24 December 2006 08:24 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think you didn't understand what Ross told you. This is your sequence:
A - create table 1
B - create table 2
C - insert into table 2 rows selected from table 1 -> this will do nothing because table 1 is still empty
D - insert into table 1

So how did you think that Oracle will automagically know what you meant to put into table 1 so that it could use it?

To make it work, substitute steps C and D.

But! Having referential integrity constraint created as you did it will not allow you to do that either (because you can't insert date into table 2 if there's no parent record in table 1). To solve this problem, you could create a foreign key as

FOREIGN KEY (id1) REFERENCES DUMMYTABLE1(id1)
ON DELETE CASCADE
INITIALLY DEFERRED DEFERRABLE

which will allow you to insert records although parents don't exist. Then insert parent records and COMMIT after everything is completed.

However, INSERT INTO table 1 will fail because there is no 'days' column in table 2. Also, 2nd and 3rd INSERT INTO table 1 will fail because of the TOO-MANY-ROWS error.

It seems that you'll have to rethink and tweak those steps in order to make it work.
Re: SELECT within INSERT [message #210982 is a reply to message #210934] Sun, 24 December 2006 09:30 Go to previous messageGo to next message
avion
Messages: 11
Registered: December 2006
Junior Member
thanks for your reply.

I took out some fields etc just to make the tables look more simple. Obviously I took out the wrong one missed out the 'days' field:)


I done what you suggested and indeed I'm getting SQL Error: ORA-00913: too many values error now. Sad

[Updated on: Sun, 24 December 2006 09:31]

Report message to a moderator

Re: SELECT within INSERT [message #211039 is a reply to message #210934] Mon, 25 December 2006 14:47 Go to previous messageGo to next message
avion
Messages: 11
Registered: December 2006
Junior Member
Can anyone help me out with the above problem please?


Thanks

[Updated on: Mon, 25 December 2006 14:47]

Report message to a moderator

Re: SELECT within INSERT [message #211040 is a reply to message #211039] Mon, 25 December 2006 15:06 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is one of queries that ends up with an error:
INSERT INTO DUMMYTABLE1 
(id1,testvalues) 
 VALUES
('2', (SELECT DUMMYTABLE2.testvalues 
       FROM DUMMYTABLE2, DUMMYTABLE1 
       WHERE DUMMYTABLE1.id1 = DUMMYTABLE2.id1
      )
);

This is query which returns more than one record:
SELECT DUMMYTABLE2.testvalues 
FROM DUMMYTABLE2, DUMMYTABLE1 
WHERE DUMMYTABLE1.id1 = DUMMYTABLE2.id1

It returns two values: 5 and 6.

Now, which one of them do you like better? I don't know, but you should. When you decide which value is to be used, modify this SELECT statement in order to select only desired one.
Re: SELECT within INSERT [message #211383 is a reply to message #211040] Thu, 28 December 2006 05:35 Go to previous message
avion
Messages: 11
Registered: December 2006
Junior Member
Thanks LittleFoot Smile
Previous Topic: update stock
Next Topic: Passing column name and getting column value
Goto Forum:
  


Current Time: Fri Dec 02 23:00:09 CST 2016

Total time taken to generate the page: 0.07102 seconds