update query Error Result

Hi All:
I've two table Cashier and proforma, I'd like to update field some fields in cashier according to the value of proforma. I've run the query everything is OK except On field  (Paid).
structurethe query i've wrote as following
        With DM.MyQuery4 Do
        Begin
                Close;
                SQL.Clear;
                SQL.Add('update cashier a inner join proforma b on ((b.tktno=a.docno) and (a.status=1)) set  a.paid=b.amount, a.invno= :x66, a.invstatus=:x22,a.dueamount=0, a.Cashierid=:x55 , a.receiptno= :x33, a.cashdate= :x44 ');
                ParamByName('x22').asinteger:=1;
                ParamByName('x55').asstring:=MF.GenUserSign;
                ParamByName('x33').asstring:=L1.Text;
                ParamByName('x44').AsDate := STRTODATE(FormatDateTime('dd/mm/yyyy',DTP1.DATE));
                ParamByName('x66').asinteger:=Strtoint(xgeninvno);
                Execute;
        End;

Open in new window


the result of the query is OK except the last record as shown in the following image
error result
khaled salemSoftware DeveloperAsked:
Who is Participating?
 
Daniel WilsonConnect With a Mentor Commented:
I think you've got the wrong join criterion.

update cashier a inner join proforma b on ((b.docno=a.docno) and (a.status=1)) set  a.paid=b.amount, a.invno= :x66, a.invstatus=:x22,a.dueamount=0, a.Cashierid=:x55 , a.receiptno= :x33, a.cashdate= :x44 ');
               

In your example proforma has no tktno field.  I'm guessing that the real one has that field and it does not always equal docno.
0
 
jogosCommented:
Previous comment could be the one.
In an update especialy when it is more complex with the join is not so easy to pinpoint errornas in a select.
As a general rule i write the update first as a select, including at least  the join and filter columns columns to check if the basics of the query are good.
0
 
Daniel WilsonCommented:
As a general rule i write the update first as a select, including at least  the join and filter columns columns to check if the basics of the query are good
.

Yes, an excellent practice that has saved me much embarrassment!
0
 
David ToddSenior DBACommented:
Hi,

If you format it right, you can leave the select version of the query in but just commented out.

as in
update e
    set b = something
-- select b
from dbo.SomeTable e
where
    somewhereclause
;

so the select can be rerun for debugging purposes, but the update is what is executed in the procedure ...

HTH
  David
0
 
khaled salemSoftware DeveloperAuthor Commented:
Hi:
 i am trying to select everything before update the query return perfect result. " This is strange case" by simple words there is two fields (paid, amount) , i would like to set paid = amount joining on b.docno= a.docno .  its seems so easy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.