Select using SQL Plus

[Gwynhwfar]

Hi folks,
This is the first time I've posted here so please bear with me. I have written the following (to run in SQL Plus)

select 
    r.relation, s.stud_id, pl.fullname, sl.fullname, ca.address_id, ca1.address_id
from
    relative r,
    people p,
    student s,
    people_lookup pl,
    student_lookup sl,
    corresp_address ca,
    corresp_address ca1
where
    r.guard_id = p.person_id and
    r.stud_id = s.stud_id and
    p.person_id = pl.person_id and
    s.stud_id = sl.stud_id and
    s.stud_id = ca.entity_id and
    p.person_id = ca1.entity_id and
    r.relation = 'car' and
    ca1.address_id=ca.address_id

What I'm trying to do is (eventually) update the relative table (field=fin_resp - not included above cos there's currently nothing in it!) where the student and guardian/person have the same address and where the the guardian/person is a CARer.
What am I doing wrong and what could I do to get what I want? Thanks for any help.

 

[haste]

If your aim is to update the relative table use the UPDATE TABLE stmt with a subquery e.g

UPDATE RELATIVE
SET fin_resp =
(
SELECT stmt
)
[WHERE CLAUSE]

 

[Gwynhwfar]

Yes this is what I will be doing, once I get the Select stmt bit correct. The one I listed above doesn't work. Doesn't like the last line. I wonder if this is because it's the same table?
Thanks

 

[SantaMufasa]

The one I listed above doesn't work. Doesn't like the last line.

What evidence is there that Oracle doesn't like the last line? Did you receive a syntax error, a run-time error, or were the results incorrect (logic error)? (My crystal ball is a bit out of focus today.)

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.