SQL & PL/SQL

I'm really hoping someone will be able to help. I need to get this figured out!

I'm trying to run an update statement that is giving me issues. I keep getting the error "single row query returns more than one result". If I tell the WHERE clause which taxidno to update specifically as shown at the very bottom, it runs fine...but I don't want to tell it each one to update, I want it to update all that qualify the WHERE clause.

I think the problem is that my subquery returns more than one result and I need the last WHERE clause to tell the subquery exactly which taxid I need from the subquery results. So in effort to link the last WHERE clause to the subquery I tried only

"WHERE c1.taxidno = p.taxid"

That returns "unknown field p.taxid" which I'm assuming is because the p.taxid is within the subquery is a variable out of range or out of context of the final WHERE clause. I've tried using WHERE NOT EXISTS or WHERE EXISTS, but nothing so far has worked.

Can anyone please shed some light on how to do this?

Translation of SQL - Basically, I'm trying to take data(a credit score) from one table and put it into another based on the most recent credit score using taxidno as the primary key. Credit score is in table #3, but persnbr (which has taxid) will link table #2 and table #3...so from there I need to take the credit score and insert into table #1.

Most of my problems seem to come with the final WHERE clause.


UPDATE cust c1
SET c1.newcreditscore = (SELECT distinct pers.CREDITSCORE
FROM PERS p, PERSCREDITSCOREHIST pers
WHERE pers.SCOREDATE = (SELECT MAX(a.SCOREDATE) FROM PERSCREDITSCOREHIST a WHERE pers.PERSNBR = a.PERSNBR)
AND p.persnbr = pers.PERSNBR)
WHERE exists (SELECT p.taxid FROM pers p WHERE replace(c1.taxidno, '-') = p.taxid);

#add this to the last WHERE clause and remove the exists clause and it works fine...the problem is I need it to do it automatically for all taxidno's.

WHERE c1.taxidno = '555555555';