Help on update that subselects other records in table, uses joins - Mailing list pgsql-general

From Jeff Kowalczyk
Subject Help on update that subselects other records in table, uses joins
Date
Msg-id pan.2003.10.29.14.52.49.915529@yahoo.com
Whole thread Raw
List pgsql-general
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)

pgsql-general by date:

Previous
From: Martijn van Oosterhout
Date:
Subject: Re: Performace question
Next
From: "Edwin Quijada"
Date:
Subject: Error size varchar