sql help 1

[TheDash]

MASTER table
Columns: SSN EMAIL

EMPDET table
Columns: EMPID EMAIL

EMP table
Columns: EMPID SSN

Need to populate email column in EMPDET table from email in MASTER table. EMPDET table has no SSN. So, EMP has to be joined. Can we do it using SQL? Or is PL/SQL necessary? If so, can someone suggest a query? Thanks in advance. P.S Its not a HW question.

 

[SantaMufasa]

Dash,

First, it is bad form to be duplicating the same information in multiple tables...it disobeys Second Normal Form.

Here are some sample data that emulates your situation:

SQL> select * from master;

SSN         EMAIL
----------- ------------------
555-12-1212 sample@domain.com
444-12-1212 another@domain.com

SQL> select * from empdet;

     EMPID EMAIL
---------- -------------------
         1
         2

SQL> select * from emp;

     EMPID SSN
---------- -----------
         1 555-12-1212
         2 444-12-1212

Here is a query that matches (rather simply) your data in the above tables:

select e.empid, e.ssn, m.email
from emp e, empdet ed, master m
where m.ssn = e.ssn
  and e.empid = ed.empid;

EMPID SSN         EMAIL
----- ----------- ------------------
    1 555-12-1212 sample@domain.com
    2 444-12-1212 another@domain.com

With the above, simple query, you should not need to (inappropriately) populate the "empdet" table with data that you duplicate from the "master" table.

If, however, you insist on duplicating "empdet" with email addresses from the "master" table, you can do it in this fashion:

update empdet edouter
   set email = (select m.email
                  from emp e, empdet ed, master m
                 where m.ssn = e.ssn
                   and e.empid = ed.empid
                   and ed.empid = edouter.empid);

2 rows updated.

select * from empdet;

EMPID EMAIL
----- ------------------
    1 sample@domain.com
    2 another@domain.com

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[TheDash]

Thanks Santa, I will use it and will let you know how it went.