Update with sub-query producing >1 row 1

[cjashwell]

Hi there,

I've tried searching for the answer to this, but haven't found what I'm looking for, so I would really appreciate any help anyone can offer.

I am trying to produce an update query to update a value in one table (Address) from another table (Pupil) which are joined (many-to-many relationship) by a third table Pupil_Address. I want to update the field Address.LEA_no with the value of Pupil.Responsible_LEA where the value Pupil_Address.Main_indicator is 'M' and the values of Pupil.Responsible_LEA and Address.LEA_No are not equal.

I know how to do this in MS Jet SQL, but I can't implement it in Oracle. I've tried two approaches.

First, I tried this:

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)

which returns the error "ORA-00904: "PUPIL"."RESPONSIBLE_LEA": invalid identifier", which I would expect.

My second approach was:

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)

But this produces "ORA-01427: single-row subquery returns more than one row".

I think I understand why this approach fails, because the sub-query in the set clause itself produces more than 1 row, so the db has no way of determing which of these rows' values should be used in the update. I need to corrolate this sub-query with the sub-query in the where clause of the update-set statement. But I have no idea how to do this!

If anyone could offer me any guidance I'd be very grateful.

Best wishes,
cja

 

[SantaMufasa]

CJ,

The reason you haven't had any responses yet is because we don't want to steer you wrong on a fairly complex set of UPDATE criteria. I believe you will get responses from us (including from me) if you can demystify the statement with a "pseudo-code"/strictly-English translation of your code. Something like:

"UPDATE each of ADDRESS table's LEA_NOs
with the value of PUPIL table's RESPONSIBLE_LEA
when PUPIL table's PUPIL_ID matches...et cetera, et cetera"

Part of our disadvantage as Oracle-ites is that we traditionally do not use "INNER JOIN/OUTER JOIN" syntax explicitly. By default, Oracle does INNER JOINS; for OUTER JOINS, we use "(+)" notation. So, since pseudo-code is fairly universal, if you re-state using such, we can probably help you better with less chance of our fouling you up.

Looking forward to your update.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:29 (20Oct04) UTC (aka "GMT" and "Zulu"), 09:29 (20Oct04) Mountain Time)

 

[cjashwell]

Thanks very much SantaMufasa,

I'll try to re-state my problem as clearly as I can:


UPDATE each of ADDRESS table's LEA_NOs
with the value of PUPIL table's RESPONSIBLE_LEA
when PUPIL_ADDRESS table's PUPIL_IDs match PUPIL table's PUPIL_IDs
and ADDRESS table's ADDRESS_IDs match PUPIL_ADDRESS table's ADDRESS_IDs
and PUPIL_ADDRESS table's LEA_NOs don't equal ADDRESS table's LEA_NOs
and PUPIL_ADDRESS table's MAIN_ADDRESS_IND = 'M'

Here's some sample data (I can't format a table properly here, but there are four columns in this sample):

ADDRESS_ADD_ID PUPIL_PUP_ID PUPIL_RESP_LEA ADD_LEA_NO
10156 65972 203 305
10156 14942 203 305
25112 36878 203 209
25112 36880 203 209
37694 69288 202 203
47664 46 203 209
71272 242 203 305
71273 77792 203 303

ADDRESS_ADD_ID is ADDRESS.ADDRESS_ID
PUPIL_PUP_ID is PUPIL.PUPIL_ID
PUPIL_RESP_LEA is PUPIL.RESPONSIBLE_LEA
ADD_LEA_NO is ADDRESS.LEA_NO

I'd be more than happy to clarify this further - please let me know if you'd like me to!

Thanks,
cja

 

[SantaMufasa]

CJ,

I believe I understand it all now, except for the concept of "LEA_NO" and "RESPONSIBLE_LEA"...What is the significance/meaning of "LEA"? (...Just wanting to confirm proper normalisation.)

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 17:37 (20Oct04) UTC (aka "GMT" and "Zulu"),
@ 10:37 (20Oct04) Mountain Time

 

[cjashwell]

Hi there,

'LEA_No' is a three-digit code representing the local education authority that an address falls within. Each Pupil also has a 'responsible LEA' which has been accurately maintained by users, and the address should normally have the same LEA_No. The LEA_No field in the Address table has NOT been maintained, and is full of much invalid data. I want to update the LEA_No in the Address table (which hasn't been accurately maintained) with the 'Responsible_LEA' value from the Pupil table (which has been).



As an aside, in theory one could argue that Address is the only table where the LEA_No should be held, as LEA_No is an attribute of address. Once my data is up-to-date and accurate, however, there may be some Pupils whose 'responsible lea' differs from that of their address, hence the need for the field in both tables. I may need to run queries occasionally which pick out pupils who, legitimately, have a different 'responsible LEA' from the "owning" LEA of their physical address. Before I get to this stage, however, I simply want to update the address table's LEA_No with the properly maintained Pupil.Responsible_LEA value.

Sorry for being long-winded, but I hope this is reasonably clear.

I very much appreciate your input.

Thanks,
Corin

 

[SantaMufasa]

Corin,

Excellent explanation and well substantiated. I shall set about now to create some code that will work for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 18:21 (20Oct04) UTC (aka "GMT" and "Zulu"),
@ 11:21 (20Oct04) Mountain Time

 

[SantaMufasa]

Corin,

Actually, I see an issue which you can help clarify:

"...and PUPIL_ADDRESS table's LEA_NOs don't equal ADDRESS table's LEA_NOs..."

What is LEA_NO doing in the cross-reference table, PUPIL_ADDRESS? I can understand LEA_NO as an attribute in ADDRESS, and I can understand RESPONSIBLE_LEA as an attribute of PUPIL, but I cannot understand why you are duplicating a value in the cross-reference table. And even if we could justify a PUPIL_ADDRESS.LEA_NO, should its value (from a business perspective) align with PUPIL or with ADDRESS?

Once we resolve this design issue, I can proceed with code.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 18:29 (20Oct04) UTC (aka "GMT" and "Zulu"),
@ 11:29 (20Oct04) Mountain Time

 

[cjashwell]

Hi there,

That was my mistake above. The LEA_No is not in the PUPIL_ADDRESS table. It only exists in the Address table and in the Pupil table. I should have said:

PUPIL table's LEA_NOs don't equal ADDRESS table's LEA_NOs

Many apologies!

 

[SantaMufasa]

Corin,

Here is a solution for you:

Section 1 -- Three-table join of pre-UPDATE image of sample data (with "expected-update-behavior" comments):

col a heading "Pupil|Table's|PUPIL_ID" format 999
col b heading "Pupil|Table's|RESPONSIBLE_ID" format 999
col c heading "Address|Table's|ADDRESS_ID" format 999
col d heading "Address|Table's|LEA_NO" format 999
col e heading "Address|Type" format a7
select p.pupil_id a, responsible_lea b, a.address_id c, lea_no d, address_type e
  from pupil p, address a, pupil_address pa
 where p.pupil_id = pa.pupil_id
   and a.address_id = pa.address_id
/

   Pupil          Pupil    Address Address
 Table's        Table's    Table's Table's Address
PUPIL_ID RESPONSIBLE_ID ADDRESS_ID  LEA_NO Type
-------- -------------- ---------- ------- -------
     101              1         10       1 M <-- No change: LEAs match
     102              1         20       3 M <-- Change: 'M' plus LEAs don't match
     102              1         30       3 A <-- No change: not 'M' type
     103              2         30       3 M <-- Change: 'M' plus LEAs don't match
     104              2         40       3 A <-- No change: not 'M' type
     105              2         50       2 M <-- No change: LEAs match

6 rows selected.
**********************************************************************************

Section 2 -- UPDATE code and results:

update address outside set lea_no =
	(select responsible_lea
	   from pupil p, address a, pupil_address pa
	  where p.pupil_id = pa.pupil_id
            and a.address_id = pa.address_id
	    and a.address_id = outside.address_id
	    and pa.address_type = 'M')
where exists
	(select 'whatever'
	   from pupil p, address a, pupil_address pa
	  where p.pupil_id = pa.pupil_id
            and a.address_id = pa.address_id
	    and a.address_id = outside.address_id
	    and pa.address_type = 'M'
	    and p.responsible_lea <> a.lea_no)
/

2 rows updated. <-- As expected.

(Post-UPDATE results)
select p.pupil_id a, responsible_lea b, a.address_id c, lea_no d, address_type e
  from pupil p, address a, pupil_address pa
 where p.pupil_id = pa.pupil_id
   and a.address_id = pa.address_id
/

   Pupil          Pupil    Address Address
 Table's        Table's    Table's Table's Address
PUPIL_ID RESPONSIBLE_ID ADDRESS_ID  LEA_NO Type
-------- -------------- ---------- ------- -------
     101              1         10       1 M
     102              1         20       1 M <-- Changed
     102              1         30       2 A
     103              2         30       2 M <-- Changed
     104              2         40       3 A
     105              2         50       2 M

6 rows selected.

Pseudo-code explanation of UPDATE code:
UPDATE the ADDRESS table's lea_no
with the PUPIL table's RESPONSIBLE_LEA that results from joining all three tables and limiting results to just the MAIN ('M') address record for a student,
but only update those rows from the first join and if the PUPIL table's RESPONSIBLE_LEA does not match the ADDRESS table's LEA.

Don't be frightened by the "WHERE EXISTS..." code. (It can be very useful.) It simply means "For the current row, did anything result from the given SELECT." It was my method of limiting UPDATEs to just the rows that successfully join, but do not have matching LEAs.

Let us know if this meets with your satisfaction.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 20:37 (20Oct04) UTC (aka "GMT" and "Zulu"),
@ 13:37 (20Oct04) Mountain Time

 

[cjashwell]

Wow - thank you so much SantaMufasa! As I'm in England, I'm away from the office now (your sig knows what time it is here!), but I will try your solution first thing tomorrow morning, and I'll let you know how I get on.

Best wishes,
Corin

 

[cjashwell]

I modified the set statement to include the condition

and p.responsible_lea <> a.lea_no

so that it matches the subquery in the where condition.

Unfortunately, I'm still getting the error:

ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

I know that both subqueries select the same 13 rows, which is the correct number meeting the conditions in my test database.

I am going to tinker with it this afternoon to see if I can get it to work.

I do understand the 'exists' statement, but I'm not certain I understand why the address table is matched to itself in the subquery:

and a.address_id = outside.address_id

I'll post again with results once I've tried a few modifications. Many thanks for all your help.

 

[SantaMufasa]

Corin,

If you can post the 13 rows, then it is likely I/we can offer a solution. Our problem is the relative "blind flying" resulting from lack of familiarity with your data.

Also,

"...I'm not certain I understand why the address table is matched to itself in the subquery..."

I used that clause to ensure that any rows returned in the "...exists..." result set matched the current row being updated.

Looking forward to assisting with a resolution.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:24 (21Oct04) UTC (aka "GMT" and "Zulu"),
@ 09:24 (21Oct04) Mountain Time

 

[cjashwell]

Hi there,

I created some 'dummy' tables and I tried the code you provided against them, and it worked perfectly. I therefore think the problem must be something in my actual tables.

I can't see anything that should affect the update, but I don't know whether the fact that the Primary Key in the pupil_address table is made up of pupil_id, address_id and address_start_date would cause any problems.

Any thoughts?

 

[SantaMufasa]

Corin,

No, your composite PK should not cause any problems. Post your 13-row result set and we'll have a look. (To preserve privacy, you needn't post addresses.)

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:30 (21Oct04) UTC (aka "GMT" and "Zulu"),
@ 09:30 (21Oct04) Mountain Time

 

[cjashwell]

(I wrote that last bit before seeing your replay, SantaMusafa!)

Here's the actual data:

RESPONSIBLE_LEA	PUPIL_ID	ADDRESS_ID	LEA_NO	MAIN_ADDRESS_IND
203		65972		10156		305		M
203		14942		10156		305		M
203		36878		25112		209		M
203		36880		25112		209		M
203		46		47664		209		M
203		242		71272		305		M
202		69288		37694		203		M
203		77792		71273		303		M
303		77768		71248		203		M
203		26196		17864		351		M
303		942		854		203		M
209		77683		47661		203		M
303		51542		41018		203		M

(Still can't format tables on here ...)

Returned from the following select:

select p.RESPONSIBLE_LEA, p.PUPIL_ID, a.ADDRESS_ID, a.LEA_NO, pa.MAIN_ADDRESS_IND
from pupil p, address a, pupil_address pa
where p.pupil_id = pa.pupil_id
and a.address_id = pa.address_id
and a.address_id = a.address_id
and pa.MAIN_ADDRESS_IND = 'M'
and p.RESPONSIBLE_LEA <> a.LEA_NO

I changed the alias of ADDRESS for the select to work. I appreciate in the UPDATE query ADDRESS is aliased as 'outside' in the update statement so that it can be joined "to itself" in the sub queries.

As I said above, the code you provided works perfectly with 'dummy' tables, so I believe that the problem must lie in my actual tables. But I could well be wrong.

Thanks,
Corin

 

[SantaMufasa]

Corin,

Upon perusal of your 13 rows, I cannot see how your are receiving "ORA-01427: single-row subquery returns more than one row"...You have no duplicate PUPIL_IDs in your data that such an error would require.

Since you've proved validity of the code on dummy data, and I cannot truly replicate your non-dummy data to help isolate the problem, I'll have to leave the detective work up to you, Corin.

Please advise us of your troubleshooting findins.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 17:31 (21Oct04) UTC (aka "GMT" and "Zulu"),
@ 10:31 (21Oct04) Mountain Time

 

[cjashwell]

I agree - I need to do some more 'digging'! I will let you know how I get on ...

I would like to express my gratitude for your help. I think a star is more than appropriate, as your code itself certainly works.

Best wishes,
Corin

 

[SantaMufasa]

Thank you, Corin. I am, as I mentioned, very eager to know the cause of your error. Once you isolate it, please do post your findings here. I'd be happy to modify the code to prevent the error while preserving your desired results.

Best regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 20:09 (21Oct04) UTC (aka "GMT" and "Zulu"),
@ 13:09 (21Oct04) Mountain Time