Re: Help on update that subselects other records in table, uses joins

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]-------------------------------------------------------