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 123456SALE 10.00
2 123456S&H (update from 1)
3 123457SALE 15.00
4 123457EXPEDITE 5.00
5 123457S&H (update from 3)
6 123458SALE 20.00
7 123458S&H (update from 6)
8 123459SALE 10.00
9 123459S&H (update from 8)
---
[orders]---
orderid | customerinvoiceid
---
12345654321
12345754321
12345854321
12345955543
---
[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: ; 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 SALE10.
409073 3 123457 SALE15.
409074 4 123457 EXPEDITE5.
409076 6 123458 SALE20.
409078 8 123459 SALE10.
\.
--
-- 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_o
