Home » SQL & PL/SQL » SQL & PL/SQL » 2 subquery for INSERT INTO (oracle 9i,winXP)
2 subquery for INSERT INTO [message #329360] Wed, 25 June 2008 03:34 Go to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
i got 2 table now namely table1,table2 where table 2 is actually a duplicated table for table1 with additional 4 columns(ID,DATE,STATUS,USERID)

the rest of the columns in table2 is just a copy from table1
the additional columns:
ID = autonum(trigger)
DATE = SYSDATE (default)
STATUS and USERID will be defined during insertion.

what i want is that, when i edit any records in table1, i wanna keep track of the modification by insert a copy of records into table2 with additional 4 info. so how i m going to query for the insertion of table2

this is my tested code but it is not working.i wonder how the insertion works if i need to query in (fields) and (values) for INSERT statement

test 1.
Insert into table2((select * from table1),STATUS,USERID) values
((select * from table1 where NAME = 'LOAN' and STAGECAT= 'EXCEPTION'),'UP','JEFF')

test 2.
Insert into table2
Select table1.*,'UP','JEFF' from table1

PS:hopefully i dont have to seperate it into 2 insert statements.
Re: 2 subquery for INSERT INTO [message #329364 is a reply to message #329360] Wed, 25 June 2008 03:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't want to query from a table in a trigger that is handling an insert/update on that table.

Do something like:
CREATE OR REPLACE TRIGGER my_trg
AFTER INSERT ON table1 FOR EACH ROW
BEGIN
  insert into table2 (<list of columns>)
  values
  (:new.col_1,:new.col_2.....);
end;
Re: 2 subquery for INSERT INTO [message #329366 is a reply to message #329364] Wed, 25 June 2008 03:59 Go to previous messageGo to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
JRowbottom wrote on Wed, 25 June 2008 16:48
You don't want to query from a table in a trigger that is handling an insert/update on that table.

Do something like:
CREATE OR REPLACE TRIGGER my_trg
AFTER INSERT ON table1 FOR EACH ROW
BEGIN
  insert into table2 (<list of columns>)
  values
  (:new.col_1,:new.col_2.....);
end;



my problem is not with the auto trigger part
my problem is how to do the insert statement
the actual tables having more than 40 columns so i make an example here:

for example, table1 having columns (NAME,STAGECAT,STEPCAT)
table 2 having colums (NAME,STAGECAT,STEPCAT,ID,DATE,STATUS,USER)

so now when there is an edit in table1
i wanna keep track the edit, and copy the record updated (NAME,STAGECAT,STEPCAT) from table1 to table2 but with additional (ID,DATE,STATUS,USER) as information.

as i said, the actual table got more than 40 columns, so i wish to have subqueries to help in insert stmt.
Re: 2 subquery for INSERT INTO [message #329368 is a reply to message #329366] Wed, 25 June 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom's solution perfectly works for your problem.
Think about it and try it.
The number of columns is irrelevant.

Regards
Michel
Re: 2 subquery for INSERT INTO [message #329379 is a reply to message #329368] Wed, 25 June 2008 04:36 Go to previous messageGo to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
Michel Cadot wrote on Wed, 25 June 2008 17:10
JRowbottom's solution perfectly works for your problem.
Think about it and try it.
The number of columns is irrelevant.

Regards
Michel


actually,
my insertion is not going to place in the trigger
i m actually doing this in php.
so now i really need a simplify insert sql stmt with subqueries that can help me.

something like:
Insert into table2
select * from table 1 where NAME='LOAN' and STAGECAT='FINE'

because table2 got extra 4 more columns, so with the stmt like that will cause error:not enough value

some more 2 out of the 4 extra columns is not required to be set when insertion.ID will be autonum and DATE will be SYSDATE by default.

hopefully the problem is understand-able.
Re: 2 subquery for INSERT INTO [message #329380 is a reply to message #329379] Wed, 25 June 2008 04:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Seeing as you're not inserting every column in table 2 (your ID is populated from a trigger, try:
INSERT INTO table2 (<list of columns) (SELECT t.*,sysdate,status,user_id FROM table1 t WHERE...
Re: 2 subquery for INSERT INTO [message #329383 is a reply to message #329380] Wed, 25 June 2008 05:11 Go to previous messageGo to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
JRowbottom wrote on Wed, 25 June 2008 17:42
Seeing as you're not inserting every column in table 2 (your ID is populated from a trigger, try:
INSERT INTO table2 (<list of columns) (SELECT t.*,sysdate,status,user_id FROM table1 t WHERE...



yeah thanks alot, this work fine for me

but is there anyway i can subquery the (<list of columns>) also
since the columns are identical other than the last 4. I saw some post says that it is possible to make 2 subQeury for insertion.Is that true?

if not my statement will be very very long.. just to name all the fields there. If i paste the fields here, it will be 12 lines.

i tried to subquery in the list of columns but it failed.
the code i did was something like this:

INSERT INTO table2 ((select * from table1),STATUS,NAME)
(SELECT t.*,'UP','JEFF' FROM table1 t WHERE ....)

I saw some post says that it is possible to make 2 subQeury for insertion.Is that true?



Re: 2 subquery for INSERT INTO [message #329389 is a reply to message #329383] Wed, 25 June 2008 05:29 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
You can do it like:
insert into tab3
select a.*,b.*,'xyz',999
from (select col1, col2, col3 from tab1) a, 
     (select col4, col5, col6 from tab2) b
Re: 2 subquery for INSERT INTO [message #329397 is a reply to message #329389] Wed, 25 June 2008 05:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, the OP can't do it like that, because they're not populateing all the columns of table2.

You can use something like
SELECT column_name FROM user_tab_columns WHERE table_name = <...>
to get you a list of column names that you can cut and paste into your code, to save typing.

I really don't understand what you mean when you say 'it is possible to make 2 subQeury for insertion'
Re: 2 subquery for INSERT INTO [message #329409 is a reply to message #329379] Wed, 25 June 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
my insertion is not going to place in the trigger
i m actually doing this in php.
so now i really need a simplify insert sql stmt with subqueries that can help me.

This will not work, between the 2 statements the data may have changed.
Trigger is the way to do it.

Regards
Michel
Re: 2 subquery for INSERT INTO [message #329568 is a reply to message #329397] Wed, 25 June 2008 21:35 Go to previous messageGo to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
JRowbottom wrote on Wed, 25 June 2008 18:57
No, the OP can't do it like that, because they're not populateing all the columns of table2.

You can use something like
SELECT column_name FROM user_tab_columns WHERE table_name = <...>
to get you a list of column names that you can cut and paste into your code, to save typing.

I really don't understand what you mean when you say 'it is possible to make 2 subQeury for insertion'


i saw a post saying that:

insert into table(select ..)values(select..)

works.
but i tried it out it seems impossible to do so.

btw i m using TOAD, so i can just copy paste the column name with the feature inside. but the sql statement is too long and look messy when i place into my php code.>.<

thanks anyway

the reason i do this in php is because this is kinda customization purpose, so i try not to change the DB site but just do the insertion in a php function.

Re: 2 subquery for INSERT INTO [message #329579 is a reply to message #329568] Wed, 25 June 2008 23:00 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the reason i do this in php is because this is kinda customization purpose, so i try not to change the DB site but just do the insertion in a php function.

Whatever your reason is,
Michel
This will not work, between the 2 statements the data may have changed.

Regards
Michel
Previous Topic: Use of Oracle Decode function
Next Topic: Pls solve the query
Goto Forum:
  


Current Time: Mon Dec 05 19:26:36 CST 2016

Total time taken to generate the page: 0.09811 seconds