pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp on update that subselects other records in table, uses joins
6+ messages / 4 participants
[nested] [flat]
* Help on update that subselects other records in table, uses joins
@ 2003-10-29 14:52 Jeff Kowalczyk <jtk@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jeff Kowalczyk @ 2003-10-29 14:52 UTC (permalink / raw)
To: pgsql-sql
I need to adapt this an update statement to a general
form that will iterate over multiple orderids for a given
customerinvoiceid. My first concern is a form that will
work for a given orderid, then an expanded version that
will work on all orderids with a specific
customerinvoiceid as a parameter.
I'm sure appropriate joins will handle it, but I'm
not making any headway, everything comes back with
multiple tuple selected for update errors.
Any help would be greatly appreciated. Thanks.
UPDATE ordercharges INNER JOIN orders ON
orders.orderid = ordercharges.orderid
SET orderchargeasbilled =
(SELECT .065*orderchargeasbilled
FROM ordercharges
WHERE ordercharges.orderid='123456'
AND orderchargecode = 'SALE')
WHERE ordercharges.orderchargecode='S&H'
AND ordercharges.orderid = '123456'
(additional join and where for customerinvoiceid
omitted/not attempted yet)
orders:
+-orderid
| customerinvoiceid
| (...)
|
| ordercharges:
| orderchargeid
+---orderid
orderchargeasbilled
(...)
To Illustrate, this is a sample table:
[ordercharges]-------------------------------------------------
orderchargeid | orderid | orderchargecode | orderchargeasbilled
---------------------------------------------------------------
1 123456 SALE 10.00
2 123456 S&H (update)
3 123457 SALE 15.00
4 123457 EXPEDITE 5.00
5 123457 S&H (update)
6 123458 SALE 20.00
7 123458 S&H (update)
8 123459 SALE 10.00
9 123459 S&H (update)
---------------------------------------------------------------
[orders]-------------------
orderid | customerinvoiceid
---------------------------
123456 54321
123457 54321
123458 54321
123459 55543
---------------------------
(e.g. use 54321 as parameter to update 3 S&H rows in 3 orders,
but not 1 S&H row in order 123459)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help on update that subselects other records in table, uses joins
@ 2003-10-29 17:42 Josh Berkus <josh@agliodbs.com>
parent: Jeff Kowalczyk <jtk@yahoo.com>
0 siblings, 3 replies; 6+ messages in thread
From: Josh Berkus @ 2003-10-29 17:42 UTC (permalink / raw)
To: Jeff Kowalczyk <jtk@yahoo.com>; pgsql-sql
Jeff,
> UPDATE ordercharges INNER JOIN orders ON
> orders.orderid = ordercharges.orderid
> SET orderchargeasbilled =
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.
Second, your value expression for the update is a subselect which includes a
select on the table and field you are updating! This is a recursive loop and
a very bad idea; gods only know what you'll end up with.
I suggest Joe Conway's "SQL for Smarties" or "SQL Queries for Mere Mortals"
from another author.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help on update that subselects other records in table, uses joins
@ 2003-10-29 17:53 Bryan Encina <bryan.encina@valleypres.org>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 6+ messages in thread
From: Bryan Encina @ 2003-10-29 17:53 UTC (permalink / raw)
To: 'Josh Berkus' <josh@agliodbs.com>; 'Jeff Kowalczyk' <jtk@yahoo.com>; pgsql-sql
>I suggest Joe Conway's "SQL for Smarties" or "SQL Queries for Mere
Mortals"
>from another author.
I could be mistaken, but I'm pretty sure Josh means Joe Celko's "SQL for
Smarties" as I've seen him mention it before, and not Joe Conway.
-b
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help on update that subselects other records in table, uses joins
@ 2003-10-29 18:35 Josh Berkus <josh@agliodbs.com>
parent: Bryan Encina <bryan.encina@valleypres.org>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-10-29 18:35 UTC (permalink / raw)
To: bryan.encina@valleypres.org; 'Jeff Kowalczyk' <jtk@yahoo.com>; pgsql-sql
Bryan,
> I could be mistaken, but I'm pretty sure Josh means Joe Celko's "SQL for
> Smarties" as I've seen him mention it before, and not Joe Conway.
Ooops! yes, Joe Conway is a major PostgreSQL contributor and author of the
tablefunc /contrib library; Joe Celko wrote the book.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help on update that subselects other records in table, uses joins
@ 2003-10-29 19:26 Paul Ganainm <paulsnewsgroups@hotmail.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 6+ messages in thread
From: Paul Ganainm @ 2003-10-29 19:26 UTC (permalink / raw)
To: pgsql-sql
josh@agliodbs.com says...
> I suggest Joe Conway's "SQL for Smarties"
Surely that's Joe Celko?
> or "SQL Queries for Mere Mortals"
> from another author.
Michael J. Hernandez, John L. Viescas
Paul...
--
plinehan__AT__yahoo__DOT__com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help on update that subselects other records in table, uses joins
@ 2003-11-03 16:45 Jeff Kowalczyk <jtk@yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 6+ messages in thread
From: Jeff Kowalczyk @ 2003-11-03 16:45 UTC (permalink / raw)
To: pgsql-sql
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]-------------------------------------------------------
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-11-03 16:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-29 14:52 Help on update that subselects other records in table, uses joins Jeff Kowalczyk <jtk@yahoo.com>
2003-10-29 17:42 ` Josh Berkus <josh@agliodbs.com>
2003-10-29 17:53 ` Bryan Encina <bryan.encina@valleypres.org>
2003-10-29 18:35 ` Josh Berkus <josh@agliodbs.com>
2003-10-29 19:26 ` Paul Ganainm <paulsnewsgroups@hotmail.com>
2003-11-03 16:45 ` Jeff Kowalczyk <jtk@yahoo.com>
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