Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

update table with one_col = one_col in other table

SOLVED
Go to solution
Ratzie
Super Advisor

update table with one_col = one_col in other table

I am having no luck getting this to work...
update vt_mlr_related
set vt_mlr_related.related='email@hotspot.net'
where vt_mlr.tn='2041234567'
and vt_mlr.master_key=vt_mlr_related.master_key

So basically the vt_mlr_related only has the master_key and is reference to the main table by master key, but I need to update based on tn in main table (vt_mlr)
2 REPLIES
Hein van den Heuvel
Honored Contributor
Solution

Re: update table with one_col = one_col in other table

Laura,

You need SQL training, or a cookbook with SQL hints. For example: http://www.oreilly.com/catalog/sqlckbk/

Also, this question has of course nothing to do with hpux, or even Oracle, and follow up question are probably best posted in an Oracle forum.

Anyway, check out the example below.

Regards,
Hein.


SQL> create table vt_mlr_related (related varchar(40), master_key integer);
SQL> create table vt_mlr (tn varchar(10), master_key integer);
SQL> insert into vt_mlr values ('test', 1234);
SQL> insert into vt_mlr values ('2041234567', 4567);
SQL> insert into vt_mlr_related values ('empty', 1234);
SQL> insert into vt_mlr_related values ('empty', 4567);
SQL> select * from vt_mlr_related;

RELATED MASTER_KEY
------------------------------ ----------
empty 1234
empty 4567

SQL> select * from vt_mlr;

TN MASTER_KEY
---------- ----------
test 1234
2041234567 4567

SQL> update vt_mlr_related
set related='some email address'
where master_key in
(select master_key from vt_mlr
where tn = '2041234567');

1 row updated.

SQL> select * from vt_mlr_related
2 ;

RELATED MASTER_KEY
------------------------------- ----------
empty 1234
some email address 4567

SQL>
Ratzie
Super Advisor

Re: update table with one_col = one_col in other table

That was what I was looking for
Thanks