New DBA

[abutler]

I'm new to this DBA stuff and I'm completely lost on how to update one table from another when I have multiple rows - I have looked everywhere for examples and can't find anything that shows it clearly.

If I run this

update imp.unemployment iu
set location_ref =
(select location_ref
from tfcfadv.unemployment)
where location in
(select location
from tfcfadv.unemployment tu
where tu.location = iu.LOCATION)

I get the oracle error 01427 single-row subquery returns more than one row.

I understand what this means but not how to fix it! can someone help me before I pull all of my hair out.

I know it might be simple for you but its not for me!!

Thanks,

[marist89]

Without knowing the structure of your table, it is really hard to help you out. Also, if you could tell us what you are trying to do would help as well.

First, you need to figure out why your subquery is returning multiple rows. I suggest using something like COUNT(*) and HAVING to figure out why:

select location_ref, count(*) 
from tfcfadv.unemployment
group by location_ref
having count(*) > 1

Once you figure that out, you can either change your query or clean up your data.

Second, you probably want to reference iu in the subquery. Something like:

update imp.unemployment iu 
   set location_ref = (select location_ref t
      from tfcfadv.unemployment
      where t.location = iu.location) 
where location in 
(select location 
from tfcfadv.unemployment tu 
where tu.location = iu.LOCATION)

[abutler]

Thanks for your reply.

What I am trying to do :-

My imp table has new data loaded but the location_ref is null so I need to update that with the reference number from the tfcfadv table where the location name is the same. There are 712 refs that I need from the table.

I tried your query but I still get the same error message - sorry for being so dim!

Thanks, Alison

[hepinge]

i think it because your sub query return more than one rows .
oracle require single-row return by subquery for example: your subquery "select location_ref from tfcfadv.unemployment" should return single row.If not ,you cann't use subquery.

[marist89]

Originally posted by marist89

First, you need to figure out why your subquery is returning multiple rows. I suggest using something like COUNT(*) and HAVING to figure out why:

select location_ref, count(*) 
from tfcfadv.unemployment
group by location_ref
having count(*) > 1

Once you figure that out, you can either change your query or clean up your data.

[abutler]

I don't think I made myself clear! I thought you could update a table with multiple rows from another but obviously you can't.

I have re-done my query to extract the references from another table where only 1 row exists for each reference and this worked.

Thanks for your help, I'm sure I'll be back for more advice!

P.S I don't seem to be getting an email when somebody responds to my post even though I have ticked the box any ideas?

[mallard]

The email reply isn't working....actually, hasn't worked for over a year now. Don't know if it will ever be resolved or not.