Subject Re: [ib-support] Still have problems updating many-to-many in SP
Author Svein Erling Tysvær
Hmm Alan,
several things here.

First the obvious one: The subselect must return one and only one value.
IB/FB won't know which value to set if more rows are returned, hence
"Multiple rows in singleton select". In FB this can be overcome by using

But there's more here (I think, never tested code like yours myself). What
if your select returns no row at all? Should the value be set to NULL? I
don't know what IB/FB does in such a case, and I'm not too keen to find out.

So change your code to something like
Update Delitems DI
Set Inv_Nmbr=(Select first 1 InvHead.Inv_Nmbr From InvHead
where DI.Acno=InvHead.Acno
and InvHead.Delivered='N')
Where DI.Delivered='N'
and exists(select 1 from InvHead
where DI.Acno=InvHead.Acno
and InvHead.Delivered='N')

If you're not using Firebird, omit first 1 and make sure you've not got any
duplicates - or use max as Nick told you.

I've not tested the code, but hope it is enough to get you going. Tell us
if it works!

By the way, what has this got to do with the subject many-to-many and
stored procedures?


Alan J Davies wrote:
I posted a proble some 3 weeks ago about not being able to update a table
with many records from another table also with many ( but fewer ) records.
I want to update the Invoice No. in the DelItems (Delivery Items) table
with the Invoice No. from InvHead (Invoice Header)
Nick Upson suggested this option and Claudio Valderrama added a caveat that
"Relying on an implementation detail to solve an ambiguity is a recipe for
spoiled data."
This partially works for one instance of the SP, but when the user adds
more deliveries, this code sets the Inv_Nmbr field to null where it already
exists in DelItems, but does give a correct Inv_Nmbr to new DelItems.
Also when there is more than 1 DelItem this error occurs

Multiple rows in singleton select

Update Delitems
Set Inv_Nmbr=(Select InvHead.Inv_Nmbr From InvHead
Inner Join DelItems DI on DI.Acno=InvHead.Acno
Where DI.Delivered='N'
And InvHead.Delivered='N')