Thread: Help on update that subselects other records in table, uses joins
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)
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]-------------------------------------------------------
On Mon, 03 Nov 2003 11:57:18 -0500, Jeff Kowalczyk <jtk@yahoo.com> wrote: >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 The key point is that you have to deal with two instances of the ordercharges table, one having orderchargecode = 'S&H' (this is the one you want to update), the other one having orderchargecode = 'SALE' which is where the values come from. UPDATE ordercharges SET orderchargeasbilled = 0.065 * sale.orderchargeasbilled FROM orders AS o, ordercharges AS sale WHERE ordercharges.orderchargecode = 'S&H' AND ordercharges.orderid = o.orderid AND sale.orderchargecode = 'SALE' AND sale.orderid = o.orderid AND o.customerinvoiceid = '54321'; HTH. Servus Manfred
On Tuesday, November 4, 2003, at 05:45 AM, Manfred Koizar wrote:
> The key point is that you have to deal with two instances of the
> ordercharges table, one having orderchargecode = 'S&H' (this is the
> one you want to update), the other one having orderchargecode = 'SALE'
> which is where the values come from.
>
> UPDATE ordercharges
> SET orderchargeasbilled = 0.065 * sale.orderchargeasbilled
> FROM orders AS o, ordercharges AS sale
> WHERE ordercharges.orderchargecode = 'S&H'
> AND ordercharges.orderid = o.orderid
> AND sale.orderchargecode = 'SALE'
> AND sale.orderid = o.orderid
> AND o.customerinvoiceid = '54321';
Nicely done. I'd like to think I would have gotten to this eventually,
but I doubt it. I was definitely on to the two instances of
ordercharges, but I think what kept me from arriving at this was that I
didn't know how to refer to the target table in the WHERE clause.
Please correct me if I'm wrong, but the ordercharges.* in the WHERE
clause is the target ordercharges, right? Really interesting!
What I came up with was deleting and reinserting the relevant
ordercharges rows inside a transaction:
BEGIN;
CREATE TEMPORARY TABLE ordercharges_temp AS
SELECT
oc.orderchargeid,
oc.orderid,
oc.orderchargecode,
0.065 * oc2.orderchargeasbilled AS orderchargeasbilled
FROM
ordercharges AS oc,
ordercharges AS oc2,
orders AS o
WHERE
oc.orderid = o.orderid AND
o.customerinvoiceid = '54321' AND
oc.orderchargecode = 'S&H' AND
oc.orderid = oc2.orderid AND
oc2.orderchargecode = 'SALE';
DELETE FROM ordercharges WHERE
orderchargeid IN (SELECT orderchargeid FROM ordercharges_temp);
INSERT INTO ordercharges
SELECT * FROM ordercharges_temp;
COMMIT;
I think yours is much more elegant, Manfred. Thanks for providing this
solution!
Well, Jeff, if you're interested in having another (albeit longer)
option, here you go. :P
Regards,
Michael
grzm myrealbox com
On Wed, 5 Nov 2003 02:58:28 +0900, Michael Glaesemann
<grzm@myrealbox.com> wrote:
>> UPDATE ordercharges
>> SET orderchargeasbilled = 0.065 * sale.orderchargeasbilled
>> FROM orders AS o, ordercharges AS sale
>> WHERE ordercharges.orderchargecode = 'S&H'
>> AND ordercharges.orderid = o.orderid
>> AND sale.orderchargecode = 'SALE'
>> AND sale.orderid = o.orderid
>> AND o.customerinvoiceid = '54321';
>
>I'd like to think I would have gotten to this eventually,
>but I doubt it.
Next time you will. Once you manage to find out that you have to deal
with two disjoint sets of ordercharges ('S&H' and 'SALE'), the rest is
pure text manipulation.
>What I came up with was deleting and reinserting the relevant
>ordercharges rows
This might have unwanted side effects (think ON DELETE CASCADE).
You already have:
> SELECT
> oc.orderchargeid,
> oc.orderid,
> oc.orderchargecode,
> 0.065 * oc2.orderchargeasbilled AS orderchargeasbilled
> FROM
> ordercharges AS oc,
> ordercharges AS oc2,
> orders AS o
> WHERE
> oc.orderid = o.orderid AND
> o.customerinvoiceid = '54321' AND
> oc.orderchargecode = 'S&H' AND
> oc.orderid = oc2.orderid AND
> oc2.orderchargecode = 'SALE';
To transform this into an UPDATE statement (which is not standard SQL,
BTW) we have to do a few easy steps. First, the target table of the
UPDATE operation cannot have an alias.
SELECT
ordercharges.orderchargeid,
ordercharges.orderid,
ordercharges.orderchargecode,
0.065 * oc2.orderchargeasbilled AS orderchargeasbilled
FROM
ordercharges,
ordercharges AS oc2,
orders AS o
WHERE
ordercharges.orderid = o.orderid AND
o.customerinvoiceid = '54321' AND
ordercharges.orderchargecode = 'S&H' AND
ordercharges.orderid = oc2.orderid AND
oc2.orderchargecode = 'SALE';
Second, we don't care about how output expressions are named, so we
remove that alias, too.
SELECT
ordercharges.orderchargeid,
ordercharges.orderid,
ordercharges.orderchargecode,
0.065 * oc2.orderchargeasbilled
FROM
...
Third, Postgres implicitly adds the target table to the FROM clause,
so we move it from the FROM clause to after the command verb, when we
change SELECT to UPDATE.
UPDATE ordercharges SET
orderchargeid = ordercharges.orderchargeid,
orderid = ordercharges.orderid,
orderchargecode = ordercharges.orderchargecode,
orderchargeasbilled = 0.065 * oc2.orderchargeasbilled
FROM
ordercharges AS oc2,
orders AS o
WHERE
ordercharges.orderid = o.orderid AND
o.customerinvoiceid = '54321' AND
ordercharges.orderchargecode = 'S&H' AND
ordercharges.orderid = oc2.orderid AND
oc2.orderchargecode = 'SALE';
Finally we remove the redundant a=a assignments and get:
UPDATE ordercharges SET
orderchargeasbilled = 0.065 * oc2.orderchargeasbilled
FROM
ordercharges AS oc2,
orders AS o
WHERE
ordercharges.orderid = o.orderid AND
o.customerinvoiceid = '54321' AND
ordercharges.orderchargecode = 'S&H' AND
ordercharges.orderid = oc2.orderid AND
oc2.orderchargecode = 'SALE';
... which looks and behaves like what I posted before.
Servus
Manfred
Manfred, Thanks for taking the time to walk me through this. You've pointed out things I've noticed when writing queries and wondered about. (More specific comments—and commentary—below. >> What I came up with was deleting and reinserting the relevant >> ordercharges rows > > This might have unwanted side effects (think ON DELETE CASCADE). Good point. At this stage in my PostgreSQL progress, I haven't been using ON DELETE CASCADE because I like to be warned of possible deletions. But I can definitely see how it could be very useful in situations where you've thought things through much more than I have. Since I haven't been confident enough in my skill to think through all the ramifications, I've been consciously limiting myself. But someday. Someday these shackles will fall free! :) > You already have: >> SELECT >> oc.orderchargeid, >> oc.orderid, >> oc.orderchargecode, >> 0.065 * oc2.orderchargeasbilled AS orderchargeasbilled >> FROM >> ordercharges AS oc, >> ordercharges AS oc2, >> orders AS o >> WHERE >> oc.orderid = o.orderid AND >> o.customerinvoiceid = '54321' AND >> oc.orderchargecode = 'S&H' AND >> oc.orderid = oc2.orderid AND >> oc2.orderchargecode = 'SALE'; > > To transform this into an UPDATE statement (which is not standard SQL, > BTW) Bless those PostgreSQL developers who have implemented methods and means beyond those of SQL! This does seem promising. > First, the target table of the UPDATE operation cannot have an alias. <snip/> > Second, we don't care about how output expressions are named, so we > remove that alias, too. <snip/> > Third, Postgres implicitly adds the target table to the FROM clause, > so we move it from the FROM clause to after the command verb, when we > change SELECT to UPDATE. I've noticed in SELECT queries when I've neglected to include a table in the FROM clause but named it specifically in an attribute that PostgreSQL's added it. I've gone in an added it, because often I haven't been paying proper attention when writing the query (resulting in omitting the table from the FROM clause) and end up getting a result that doesn't join as I want it to, since I haven't accounted for it fully in the WHERE clause either, e.g., giving variations of each row for each possible value in the omitted table. Especially nasty when no column from the omitted but implicitly added table is targeted and I've got apparently duplicated rows in my beautiful normalized tables! I've wondered why PostgreSQL let's me make this "mistake", but now I can see definite benefits. > UPDATE ordercharges SET > orderchargeid = ordercharges.orderchargeid, > orderid = ordercharges.orderid, > orderchargecode = ordercharges.orderchargecode, > orderchargeasbilled = 0.065 * oc2.orderchargeasbilled > FROM > ordercharges AS oc2, > orders AS o > WHERE > ordercharges.orderid = o.orderid AND > o.customerinvoiceid = '54321' AND > ordercharges.orderchargecode = 'S&H' AND > ordercharges.orderid = oc2.orderid AND > oc2.orderchargecode = 'SALE'; > > Finally we remove the redundant a=a assignments and get: > > UPDATE ordercharges SET > orderchargeasbilled = 0.065 * oc2.orderchargeasbilled > FROM > ordercharges AS oc2, > orders AS o > WHERE > ordercharges.orderid = o.orderid AND > o.customerinvoiceid = '54321' AND > ordercharges.orderchargecode = 'S&H' AND > ordercharges.orderid = oc2.orderid AND > oc2.orderchargecode = 'SALE'; > > ... which looks and behaves like what I posted before. Wow. Not only smart, but able to clearly explain the route from a to b. Virtual tipple of choice to you, Manfred. If you're ever in Tokyo, please let me buy you a drink! Thanks again! Michael grzm myrealbox com
On Thu, 6 Nov 2003 00:50:06 +0900, Michael Glaesemann <grzm@myrealbox.com> wrote: >>> What I came up with was deleting and reinserting the relevant >>> ordercharges rows >> >> This might have unwanted side effects (think ON DELETE CASCADE). > >Good point. At this stage in my PostgreSQL progress, I haven't been >using ON DELETE CASCADE That was only one example. Here's another one: If the target table is the referenced table of a foreign key relationship without ON DELETE CASCADE, the unwanted side effect is that the DELETE fails. >> Third, Postgres implicitly adds the target table to the FROM clause, >> so we move it from the FROM clause to after the command verb, when we >> change SELECT to UPDATE. > >I've noticed in SELECT queries when I've neglected to include a table >in the FROM clause but named it specifically in an attribute that >PostgreSQL's added it. That's not the same. What you mean is described under add_missing_from in http://developer.postgresql.org/docs/postgres/runtime-config.html. In a SELECT you *can* omit the table name from the FROM clause, or you can even omit the whole FROM clause. In an UPDATE statement you *have to* omit the target table from the FROM clause. OTOH you *can* omit additional tables from the FROM clause: UPDATE ordercharges SET orderchargeasbilled = 0.065 * oc2.orderchargeasbilled FROM ordercharges AS oc2 /* , orders AS o */ WHERE ordercharges.orderid = orders.orderid AND orders.customerinvoiceid = '54321' AND ordercharges.orderchargecode = 'S&H' AND ordercharges.orderid = oc2.orderid AND oc2.orderchargecode = 'SALE'; In this case add_missing_from causes the orders table seen in the WHERE clause to be added to the FROM clause. Funny, isn't it? In my personal opinion this "feature" is dangerous and add_missing_from should be disabled for every 7.4 installation unless there are compatibility problems with automatically generated queries. Servus Manfred