Normally, I would have recommended you to run something like:

select weight.<primary_key_field>
from weigh
join supp_ashrates on weigh.supp_code=supp_ashrates.supp_code
join supplier on <whatever, you lack any link to this table>
where weigh.ash_pc>supp_ashrates.ash_base
and weigh.ash_pc>=supp_ashrates.ash_from
and weigh.ash_pc<=supp_ashrates.ash_to
and weigh.dollar_rate>0
group by 1
having count(*) > 1

to find weight records return more than one value (which you cannot have
in your update statement).

However, I note two things in your update statement. One of them is
easily seen when changing from implicit to explicit joins as above - you
join to supplier without telling how to join to this table. Hence, your
query will invariably fail if you have more than one supplier!

The other thing I note, is that you mention weight in both UPDATE and
(sub-SELECT) - something you definitely do not want to do unless you
qualify them (and I doubt you want to do it at all in your case).

So try something like:

update weigh
set dollar_rate=coalesce(
(select water_rate from supp_waterrates
where weigh.supp_code=supp_waterrates.supp_code
and weigh.water_pc>=supp_waterrates.water_from
and weigh.water_pc<supp_waterrates.water_to),0),
ash_deduction=coalesce(
(select
round(ceiling(supp_ashrates.ash_from-weigh.ash_pc)*supp_ashrates.ash_rate,2)
from supp_ashrates
JOIN supplier ON ... //delete the entire line unless it matters
where weigh.supp_code=supp_ashrates.supp_code
and weigh.ash_pc>supp_ashrates.ash_base
and weigh.ash_pc>=supp_ashrates.ash_from
and weigh.ash_pc<=supp_ashrates.ash_to
and weigh.dollar_rate>0),0),
net_rate=dollar_rate+ash_deduction, /* Ash_deduction is a minus */
dollar_total=tonnes*net_rate
where current_YN='Y';

And don't forget that this updates all rows with weight.current_YN='Y'
regardless of whether it matches any record in supp_waterrates and
supp_ashrates.

HTH,
Set

Alan.Davies@... wrote:

> Hi
> I have a problem updating a table in an sp.
> System is Windows 2003 server 4gb ram, stacks of disk, Firebird 1.54
> I get the error "multiple rows in singleton select" when I try to run
> this revised sp. I want to add a penalty of $0.10 for each full
> percentage point over the ash_base - generally 22% but it varies hence
> a table with the different rates.
> The tables (cutdown) are
> weigh
> supp_code ash_pc water_pc
> 3 24 34
> 3 21 31
>
> supp_ashrates
> supp_code ash_from ash_to ash_base ash_rate
> 3 0 22 22 0.00
> 3 22 100 22 0.10
>
> The water_rate part works fine and if I run this as a query on its own
> it works fine
> select
> round(ceiling(supp_ashrates.ash_from-weigh.ash_pc)*supp_ashrates.ash_rate,2)
> from supp_ashrates, weigh, supplier
> where weigh.supp_code=supp_ashrates.supp_code
> and weigh.ash_pc>supp_ashrates.ash_base
> and weigh.ash_pc>=supp_ashrates.ash_from
> and weigh.ash_pc<=supp_ashrates.ash_to
> and weigh.dollar_rate>0
>
>
> begin
> update weigh
> set dollar_rate=coalesce(
> (select water_rate from supp_waterrates, weigh
> where weigh.supp_code=supp_waterrates.supp_code
> and weigh.water_pc>=supp_waterrates.water_from
> and weigh.water_pc<supp_waterrates.water_to),0),
> ash_deduction=coalesce(
> (select
> round(ceiling(supp_ashrates.ash_from-weigh.ash_pc)*supp_ashrates.ash_rate,2)
> from supp_ashrates, weigh, supplier
> where weigh.supp_code=supp_ashrates.supp_code
> and weigh.ash_pc>supp_ashrates.ash_base
> and weigh.ash_pc>=supp_ashrates.ash_from
> and weigh.ash_pc<=supp_ashrates.ash_to
> and weigh.dollar_rate>0),0),
> net_rate=dollar_rate+ash_deduction, /* Ash_deduction is a minus */
> dollar_total=tonnes*net_rate
> where current_YN='Y';
> end
>
> I understand that there is a conflict in trying to update, but can't
> get to grips with it. Any advice will be gladly received. If a fuller
> description is needed, together with full table data, it can be
> uploaded.
> Thanks
> Alan