PL/SQL brainfart

[Patt]

Here's my problem:

The SET sparepart.aenm = (SELECT mroitem.aenm
FROM sparepart, mroitem
WHERE sparepart.item_oi = mroitem.itemoi(+))

section is returning too many rows. The sparepart table has a link to the mroitem table, and in reality what I'd like to do is set the sparepart.aenm = the mroitem.aenm where the link holds true, but do it for each item pulled back from the where criteria on the UPDATE clause. I can't believe this should be difficult, but can't remember how to do it.

edit: to clarify, I think I need to have more WHERE criteria in the subquery, but don't know how to pass in the currently processing sparepart record.

:beer: for the solution

 

[Jeraden]

Your inner select isn't referencing the row you are actually trying to update. Its going to be returning the full contents of the 2 tables joined together.

You need to remove the sparepart table from the inner select, so its joining to the table in the outer select. Something like:

 

[Patt]

:beer::beer::beer::beer:
:beer::beer::beer::beer:
:beer::beer::beer::beer:

That's a case for you Jeraden ... the simple removal of that table worked a charm.

Much appreciated for the quick response!

 

[Jeraden]

Also, 1 other thing - that will always update sparepart even if no records are returned from the inner select - so you'll end up nulling out any existing value if no match is found.
If you don't want to do that:

 

[Patt]

The inner select is actually referencing a foreign key-primary key relationship, so it has to be there, but I appreciate the extra thought.