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)
FROMorders 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
charactervarying(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]-------------------------------------------------------
