Home » SQL & PL/SQL » SQL & PL/SQL » Insert column value by select statement .. (Oracle 9i, Win XP)
Insert column value by select statement .. [message #434244] Tue, 08 December 2009 01:57 Go to next message
adilsami
Messages: 46
Registered: October 2007
Member
Hi ,

am facing problem in inserting values in to table by using the below statement , as am trying to insert a column value using the select statement:

Insert into JOBTASK (JPNUM,JPTASK,DESCRIPTION,JOBTASKID,JOBPLANID,LANGCODE)  
Values  ('FDR-0001', '10', 'ABC', JOBTASKSEQ.NEXTVAL,SELECT JOBPLANID FROM JOBPLAN P,JOBTASK T WHERE P.JPNUM=T.JPNUM,'EN');


it gives me error : ORA-00936: missing expression

i need to insert (JOBPLANID) value by select statement in the insert statement.

can any body help me with this ..

Regards,
Re: Insert column value by select statement .. [message #434245 is a reply to message #434244] Tue, 08 December 2009 02:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
INSERT...VALUES does just that - it inserts a list of values (or functions) into a table.

If you want to select data from elsewhere in the database youo need to either:
1) Wrap your select in a function, and call the function in the INSERT statement
2) Wrap your whole INSERT in a pl/sql block, select the values into local variables and then use these variables in the insert
3) Use an INSERT... SELECT, like this:
Insert into JOBTASK (JPNUM,JPTASK,DESCRIPTION,JOBTASKID,JOBPLANID,LANGCODE)  
SELECT 'FDR-0001'
      ,'10'
      ,'ABC'
      ,JOBTASKSEQ.NEXTVAL
      ,p.JOBPLANID
      ,'EN' 
FROM    JOBPLAN P
       ,JOBTASK T
WHERE  P.JPNUM=T.JPNUM;


I suspect that option 3 is what you're looking for, as the SELECT that you're using doesn't look like it's going to return just one row.
Re: Insert column value by select statement .. [message #434247 is a reply to message #434244] Tue, 08 December 2009 02:28 Go to previous messageGo to next message
adilsami
Messages: 46
Registered: October 2007
Member
Thanx for your reply..

all the inserted values are user given , only (JOBPLANID) data is coming from another table upon criteria..

any other solution ??

Regards,
Re: Insert column value by select statement .. [message #434248 is a reply to message #434247] Tue, 08 December 2009 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does this not work? Why?

Regards
Michel
Re: Insert column value by select statement .. [message #434253 is a reply to message #434244] Tue, 08 December 2009 03:01 Go to previous messageGo to next message
adilsami
Messages: 46
Registered: October 2007
Member
sorry it was my mistake .. as i was trying to join with another table which was not required ..

i solve the problem by using this query :

Insert into JOBTASK (JPNUM,JPTASK,DESCRIPTION,JOBTASKID,JOBPLANID,LANGCODE)  
Values  ('FDR-0001', '10', 'ABC', JOBTASKSEQ.NEXTVAL,SELECT JOBPLANID FROM JOBPLAN P WHERE P.JPNUM='FDR-0001','EN');


Regards,
Re: Insert column value by select statement .. [message #434254 is a reply to message #434253] Tue, 08 December 2009 03:14 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
i solve the problem by using this query :

No you didn't. What you have posted would raise an error
Re: Insert column value by select statement .. [message #434255 is a reply to message #434247] Tue, 08 December 2009 03:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As @Michel said - what's wrong with the solution given.
Previous Topic: calculate cummulative frequency
Next Topic: ORA-06502 issue after 10g migration
Goto Forum:
  


Current Time: Thu Sep 29 20:57:41 CDT 2016

Total time taken to generate the page: 0.11012 seconds