From: Jeff Kowalczyk <jtk@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Help on update that subselects other records in table, uses joins
Date: Mon, 03 Nov 2003 11:45:25 -0500
Message-ID: <pan.2003.11.03.16.45.21.454958@yahoo.com> (raw)
References: <pan.2003.10.29.14.52.52.251007@yahoo.com>
<200310290942.57584.josh@agliodbs.com>
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]-------------------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jtk@yahoo.com
Subject: Re: Help on update that subselects other records in table, uses joins
In-Reply-To: <pan.2003.11.03.16.45.21.454958@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox