Hi
I have a problem updating a table correctly within a SP - it doesn't work
in isql either.
I have 2 tables - INVHEAD with invoice # and delivery note #, and DELITEMS,
a list of parts delivered showing invoice #, delivery note #, part # etc.
The company makes deliveries daily and uses the DELITEMS table to respond
to customer queries such as 'Have you sent Part # 12345 yet?'
Delivery notes are created at random according to customer requirements
At the moment I :-
INSERT into DELTEMP (same structure as DELITEMS)
Select * from Delitems Where DelItems.Delivered='N'
then I recopy the records back after completing my other updates.
But I know this is wrong and I should be able to update what I want in the
DELITEMS table.
DNOTE_NMBR and INV_NMBR are generated by triggers and work OK

This is what happens starting with 2 empty tables and completing the
operation:-
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 N

At this stage everything is OK - DELIVERED is set to Y in both tables to
exclude from further processing
Another delivery is made, making the table like this before completion of
update

ACNO DNOTE_NMBR INV_NMBR PARTNO DELIVERED
CAP 2670 9322 28-1093 Y
CAP 2670 9322 28-1141 Y
MIP1 New New F2660/002 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

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

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

The code I am using is this:-
Update Delitems
Set DelItems.Inv_Nmbr=(Select InvHead.Inv_Nmbr From InvHead
Where DelItems.Acno=InvHead.Acno
And InvHead.Delivered='N'
And DelItems.Delivered='N),
DelItems.Dnote_Nmbr=(Select InvHead.Dnote_Nmbr From InvHead
Where DelItems.Acno=InvHead.Acno
And InvHead.Delivered='N'
And DelItems.Delivered='N);
I can see that DNOTE_NMBR and INV_NMBR are being updated incorrectly,
because they should be excluded from processing.
I have tried to use joins but get the following error with the next bit of
code:-

Dynamic SQL Error
SQL error code = -204
Ambiguous field name between table/view DELITEMS and table/view DELITEMS
ACNO

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

How can I retain existing data in DNOTE_NMBR and INV_NMBR while updating
new items?

Any help gratefully received

Alan Davies