I need to adapt this an update statement to a general
form that will iterate over multiple orderids for a given
customerinvoiceid. My first concern is a form that will
work for a given orderid, then an expanded version that
will work on all orderids with a specific
customerinvoiceid as a parameter.
I'm sure appropriate joins will handle it, but I'm
not making any headway, everything comes back with
multiple tuple selected for update errors.
Any help would be greatly appreciated. Thanks.
UPDATE ordercharges INNER JOIN orders ON
orders.orderid = ordercharges.orderid
SET orderchargeasbilled =
(SELECT .065*orderchargeasbilled
FROM ordercharges
WHERE ordercharges.orderid='123456'
AND orderchargecode = 'SALE')
WHERE ordercharges.orderchargecode='S&H'
AND ordercharges.orderid = '123456'
(additional join and where for customerinvoiceid
omitted/not attempted yet)
orders:
+-orderid
| customerinvoiceid
| (...)
|
| ordercharges:
| orderchargeid
+---orderid
orderchargeasbilled
(...)
To Illustrate, this is a sample table:
[ordercharges]-------------------------------------------------
orderchargeid | orderid | orderchargecode | orderchargeasbilled
---------------------------------------------------------------
1 123456 SALE 10.00
2 123456 S&H (update)
3 123457 SALE 15.00
4 123457 EXPEDITE 5.00
5 123457 S&H (update)
6 123458 SALE 20.00
7 123458 S&H (update)
8 123459 SALE 10.00
9 123459 S&H (update)
---------------------------------------------------------------
[orders]-------------------
orderid | customerinvoiceid
---------------------------
123456 54321
123457 54321
123458 54321
123459 55543
---------------------------
(e.g. use 54321 as parameter to update 3 S&H rows in 3 orders,
but not 1 S&H row in order 123459)
