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

Josh Berkus wrote:
> You may only UPDATE one table at a time, you can't update a JOIN. So when
> selecting from another table to filter or calculate your update, the form is:
> UPDATE orderchanges
> SET orderchargesbilled = {expression}
> FROM orders
> WHERE orders.orderid = ordercharges.orderid
> AND etc.

Thanks for the suggestions everyone, however I'm still at the same
underlying stopping point: the subselect in the SET clause returns
multiple rows, and I don't know how to make it 'iterate' on each orderid
in the specified customerinvoiceid without using a JOIN, which is itself
apparently either not directly possible or complex.

UPDATE ordercharges
SET orderchargeasbilled = (expression)
WHERE
  ordercharges.orderchargecode = 'S&H' and
  ordercharges.orderid=(SELECT orderid   (tried IN(SELECT...) as well)
     FROM orders
     WHERE customerinvoiceid = '54321');

'expression' needs to get the orderchargeasbilled for the current orderid
only, not the three rows of the sample. This is why I tried JOINs of
incorrect design *outside* the subselect.

SELECT .065 * orderchargeasbilled
FROM ordercharges, orders
WHERE ordercharges.orderid = orders.orderid AND
orders.customerinvoiceid = '54321' AND
ordercharges.orderchargecode = 'SALE'

(returns the same result (3 rows) as:

SELECT .065 * orderchargeasbilled
FROM ordercharges INNER JOIN orders
ON ordercharges.orderid = orders.orderid
WHERE orders.customerinvoiceid = '54321' AND
ordercharges.orderchargecode = 'SALE'

I'm attaching a small postgres sql dump of a sample testupdates1 database,
if anyone has an idea and wants to take a shot at it.

psql -U postgres -d testupdates1 -f testupdates1.sql

pg_dump --file=testupdates1.sql --format=p -c -o -U postgres testupdates1

[ordercharges]-------------------------------------------------
orderchargeid | orderid | orderchargecode | orderchargeasbilled
---------------------------------------------------------------
1               123456    SALE              10.00
2               123456    S&H               (update from 1)
3               123457    SALE              15.00
4               123457    EXPEDITE           5.00
5               123457    S&H               (update from 3)
6               123458    SALE              20.00
7               123458    S&H               (update from 6)
8               123459    SALE              10.00
9               123459    S&H               (update from 8)
---------------------------------------------------------------

[orders]-------------------
orderid | customerinvoiceid
---------------------------
123456    54321
123457    54321
123458    54321
123459    55543
---------------------------

[testupdates1.sql]-------------------------------------
--
-- PostgreSQL database dump
--

\connect - postgres

SET search_path = public, pg_catalog;

ALTER TABLE ONLY public.ordercharges DROP CONSTRAINT ordercharges_pkey;
ALTER TABLE ONLY public.orders DROP CONSTRAINT orders_pkey;
DROP TABLE public.ordercharges;
DROP TABLE public.orders;

--
-- TOC entry 2 (OID 0)
-- Name: Max OID; Type: <Init>; Schema: -; Owner:
--

CREATE TEMPORARY TABLE pgdump_oid (dummy integer);
COPY pgdump_oid WITH OIDS FROM stdin;
409083    0
\.
DROP TABLE pgdump_oid;

--
-- TOC entry 3 (OID 409056)
-- Name: orders; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE orders (
    orderid character varying(30) NOT NULL,
    customerinvoiceid character varying(30)
);

--
-- TOC entry 4 (OID 409062)
-- Name: ordercharges; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE ordercharges (
    orderchargeid serial NOT NULL,
    orderid character varying(30),
    orderchargecode character varying(15),
    orderchargeasbilled numeric(18,4)
);

--
-- Data for TOC entry 8 (OID 409056)
-- Name: orders; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY orders (orderid, customerinvoiceid) WITH OIDS FROM stdin;
409067    123456    54321
409068    123457    54321
409069    123458    54321
409070    123459    55543
\.

--
-- Data for TOC entry 9 (OID 409062)
-- Name: ordercharges; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY ordercharges (orderchargeid, orderid, orderchargecode, orderchargeasbilled) WITH OIDS FROM stdin;
409072    2    123456    S&H    \N
409075    5    123457    S&H    \N
409077    7    123458    S&H    \N
409079    9    123459    S&H    \N
409071    1    123456    SALE    10.0000
409073    3    123457    SALE    15.0000
409074    4    123457    EXPEDITE    5.0000
409076    6    123458    SALE    20.0000
409078    8    123459    SALE    10.0000
\.

--
-- TOC entry 6 (OID 409058)
-- Name: orders_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (orderid);

--
-- TOC entry 7 (OID 409065)
-- Name: ordercharges_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY ordercharges
    ADD CONSTRAINT ordercharges_pkey PRIMARY KEY (orderchargeid);

--
-- TOC entry 5 (OID 409060)
-- Name: ordercharges_orderchargeid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval ('ordercharges_orderchargeid_seq', 1, false);

[end]-------------------------------------------------------