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')

Cut-down version of data involved
Contents of DELITEMS (subset)
ACNO DNOTE_NMBR INV_NMBR PARTNO DELIVERED
CAP 2670 9322 28-1093 N
CAP 2670 9322 28-1141 N

Contents of INVHEAD (subset)
ACNO DNOTE_NMBR INV_NMBR INV_DATE DELIVERED
CAP 2670 9322 03/01/2002 Y
MIP1 2671 9323 03/01/2002 N - a new invoice
entered

Contents of DELITEMS (subset)
After updating this is the result!
ACNO DNOTE_NMBR INV_NMBR PARTNO DELIVERED
CAP <null> <null> 28-1093 Y // I want
to retain
CAP <null> <null> 28-1141 Y // existing
data
MIP1 2671 9323 F2660/002 N


The problem is resolved by coding the following in Delphi, using 2
IBTables, and works perfectly

// This is here because I can't get SQL version to work in
DeliveryCompletion
DelItems.MasterSource=DSInvHead
DelItems.MasterField=Acno
InvHead.Open;
DelItems.Open;
while not InvHead.eof do
begin
while not DelItems.Eof do
begin
if (DelItems['Delivered']='N') and (InvHead['Delivered']='N') then
begin
DelItems.Edit;
DelItems['Inv_Nmbr']:=InvHead['Inv_Nmbr'];
DelItems.Post;
end;
DelItems.next;
end;
InvHead.next;
end;
// end of "cheat"

Is it possible to do what I want? If so can someone please show me the way.

Regards
Alan Davies