update table with one_col = one_col in other table

[Ratzie]

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)

[Hein van den Heuvel · Accepted solution]

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]

That was what I was looking for
Thanks