Correlated subquery update - again!

Hi there,

I appreciate this questions has come up many, many times, but I can't find a solution that I can apply to my particular case.

I have three tables, Pupil, Pupil_Address and Address. Pupil_Address links Pupil and Address as a many-to-many relationship, on Pupil.Pupil_Id = Pupil_Address.Pupil_Id, Address.Address_Id = Pupil_Address.Address_Id.

I want to update a field in Address with a value from Pupil where two conditions are met.

Here is the code I have tried so far:

update address
set address.LEA_NO = pupil.RESPONSIBLE_LEA where address.ADDRESS_ID in
(select address.ADDRESS_ID

   FROM (Address INNER JOIN pupil_address ON Address.Address_ID = Pupil_Address.Address_ID)
  INNER JOIN Pupil ON Pupil_address.Pupil_ID = Pupil.Pupil_ID   where pupil_address.MAIN_ADDRESS_IND = 'M'   and pupil.RESPONSIBLE_LEA <> address.LEA_NO)

Predicatbly, this produces a "ORA-00904: "PUPIL"."RESPONSIBLE_LEA": invalid identifier" error.

Next, I tried this:

update address
set address.LEA_NO =
(select pupil.RESPONSIBLE_LEA

  FROM (pupil INNER JOIN pupil_address ON pupil.PUPIL_ID = Pupil_Address.pupil_ID)
  INNER JOIN address ON address.ADDRESS_ID = Pupil_address.address_id   where pupil_address.MAIN_ADDRESS_IND = 'M'   and pupil.RESPONSIBLE_LEA <> address.LEA_NO) where exists
(select 'x'
  FROM (pupil INNER JOIN pupil_address ON pupil.PUPIL_ID = Pupil_Address.pupil_ID)
  INNER JOIN address ON address.ADDRESS_ID = Pupil_address.address_id   where pupil_address.MAIN_ADDRESS_IND = 'M'   and pupil.RESPONSIBLE_LEA <> address.LEA_NO)

This produces the error:
"ORA-01427: single-row subquery returns more than one row"

I have also tried this:

update address
set address.LEA_NO =
(select pupil.RESPONSIBLE_LEA

  FROM (pupil INNER JOIN pupil_address ON pupil.PUPIL_ID = Pupil_Address.pupil_ID)
  INNER JOIN address ON address.ADDRESS_ID = Pupil_address.address_id   where pupil_address.MAIN_ADDRESS_IND = 'M'   and pupil.RESPONSIBLE_LEA <> address.LEA_NO) where address.ADDRESS_ID in
(select address.ADDRESS_ID

  FROM (Address INNER JOIN pupil_address ON Address.Address_ID = Pupil_Address.Address_ID)
  INNER JOIN Pupil ON Pupil_address.Pupil_ID = Pupil.Pupil_ID   where pupil_address.MAIN_ADDRESS_IND = 'M'   and pupil.RESPONSIBLE_LEA <> address.LEA_NO)

which produces the same error, ORA-00904.

I'd be extremely grateful if anyone could give me any guidance on how to solve this problem, which I think is to do with my understanding of correlated sub-queries.

Many thanks,
cjashwell Received on Wed Oct 20 2004 - 09:11:22 CDT