Developer Forum

I need a fresh look at an UPDATE query. I have tried many things to
fix it, but it is getting too complex.
I really want this whole thing to be done in only one query. (If
possible)

Here it is:


UPDATE invc_hdr
LEFT JOIN invc_dtl ON (invc_dtl.INVC_HDR_ID = invc_hdr.ID)
LEFT JOIN pr_dtl ON (invc_dtl.S_PO_NO = pr_dtl.s_po_no AND
invc_dtl.V_ITEMNO = pr_dtl.v_itemno)
SET
pr_dtl.c_shp_qty = pr_dtl.c_shp_qty + invc_dtl.QTY_SHIP,
pr_dtl.qty_in_hs = pr_dtl.qty_in_hs - invc_dtl.QTY_SHIP,
invc_dtl.POST_C_SHP = 1,
pr_dtl.inv_date = invc_hdr.INVC_DATE,
pr_dtl.inv_nr = invc_hdr.INVC_NO
WHERE
(invc_hdr.ID LIKE $inv_ID) AND
(invc_dtl.POST_C_SHP = 0)


Explanation of names:
invc_hdr : invoice header table (invoice info,like number, date etc)
invc_dtl : invoice details table (has the record of invoices)
pr_dtl : the order details table

S_PO_NO : number of purchase order
V_ITEMNO : unique article number
c_shp_qty : TOTAL quantity shipped to customer
QTY_SHP : quantity on the invoice
qty_in_hs : quantity "in house" = in stock
POST_C_SHP : flag, if not "0", the qty of this item is not yet
transfered from in_house to in_shipment

What is it supposed to do:
After an invoice is prepared, this query is executed. It should add
the quantities to the total of shipped items for this product and this
order, and subtract that qty from the quantity in stock. It also must
mark the item in invc_dtl so that this record will not be processed
again.
It also adds the date and invoice number to the record of the order.

What is the problem:
As long as there is only 1 item with a certain number on the invoice,
this works great. However:
If there are more than 1 item with the same V_ITEMNO on the invoice,
then only 1 qty is transfered.

What is the cause:
What I think is happening is that the quantities are added /
subtracted, but not "updated in the table". The next record will start
with the original value from the field, and not with the already
changed one after the first update.


What do i need:
Something to make this work :)
I have found many remarks on this on the mySQL manual pages, and also
on other pages on the web. I cant however firgure out how to solve the
problem.
I do not want to loop through all the items outside mySQL.



Can anyone shed some light on this? Any direction to look in?
Maybe I should replace part of the query with a subquery, but then I
run into troubles with the updating of the POST_C_SHP field.


Feel free to shoot anything at it, I am open to suggestions.

Thanks already,
Richard

Oops, correction:
POST_C_SHP : flag, if "0", the qty of this item is not yet
transfered from in_house to in_shipment

(not shouldnt have been there)

R.

ichard wrote:

I generally do this with multiple queries (enclosed in a transaction).
I know you don't want to loop through items outside of MySQL - but it's
much easier to program, understand and modify. And clarity is always my
first concern.

Question for you - let's say you do get this working. Now, in a year,
you need to come back and modify it for some reason. What are the
chances you'll quickly remember what you did?

Or, worse yet, someone else has to modify it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================

"Jerry Stuckle" < XXXX@XXXXX.COM > wrote in message
news:h6fnj5$r4f$ XXXX@XXXXX.COM ...

100%. its documented.
And has been working for over 5 years and I inhereted it.

Any thoughts on the query?

R.

[...]

An update is *supposed* to map one database state onto another,
(actually I'm a bit surprised that you can at all update an outer
join). If it where possible to do what you want, it would introduce
very *** consequences.

/Lennart