insert row using column value from other table..

[eyetry]

I thought I could do this but can't figure out why it isn't working..

----------------------------------------------------------

insert into table_1
(p_key,
id, --value from column2 tbl 2
p_key_table_2 --value from column1 tbl 2 hard coded
email_addr)
value (user.p_key_seq.nextval,
select t2.column2 from table_2 t2 where t2.column1 = n,
n,
email_addr@url.com)

----------------------------------------------------------

is it possible to set the value in table_1.id this in an insert this way? If so what's wrong?

 

[Thargy]

eye,

yes it is, but you don't need the key word values, if the values are coming from a select statement, and you also don't need the parentheses.

insert into table_1
(p_key,
id, --value from column2 tbl 2
p_key_table_2 --value from column1 tbl 2 hard coded
email_addr)
select user.p_key_seq.nextval,
t2.column2,
n,
email_addr@url.com
from table_2 t2
where t2.column1 = n

or something close to the above should do the trick. I'm not quite sure what your hard-coded url is, but just include it as a text string.

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

Eyetry,

You are fairly close to the syntax you need to reach your objective. If you wish to INSERT data from a SELECT statment, then all of the values to insert must appear in the SELECT statement itself (as I have illustrated, below). Your reference to the "email_addr@url.com" certainly won't work since Oracle interprets that string as a syntactically incorrect variable name. I also created an Oracle "bind variable" to provide the value for "n":

select * from table_1;

no rows selected

select * from table_2;

   COLUMN1    COLUMN2
---------- ----------
        11        111
        22        222
        33        333

3 rows selected.

var n number
exec :n := 22
insert into table_1
  (p_key,
   id,   --value from column2 tbl 2
   p_key_table_2, --value from column1 tbl 2 hard coded
   email_addr)
       select p_key_seq.nextval, t2.column2, :n, 'email_addr@url.com'
         from table_2 t2 where t2.column1 = :n
/

1 row created.

select * from table_1;

     P_KEY         ID P_KEY_TABLE_2 EMAIL_ADDR
---------- ---------- ------------- ------------------
         1        222            22 email_addr@url.com

1 row selected.

As you can see, I used a literal string value for the e-mail address, but you can tailor the source to be from either a variable, a bind variable, a literal, or from another table.


Let us know your thoughts or if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Sorry for the double post to Tharg's excellent post...I had to stop in the middle of my composing a response to take a phone call that interrupted my focus on your need...But I guess that two nearly identical responses provide some reassurances, as well!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[eyetry]

thanks, that worked, so simple when you see it

 

[Thargy]

Santa,

I'm the one who feels reassured

Regards

T