Help on update that subselects other records in table, uses joins

[Jeff Kowalczyk]

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)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Jeff Kowalczyk]

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]-------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Manfred Koizar]

On Mon, 03 Nov 2003 11:57:18 -0500, Jeff Kowalczyk <jt*@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Michael Glaesemann]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Manfred Koizar]

On Wed, 5 Nov 2003 02:58:28 +0900, Michael Glaesemann
<gr**@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Michael Glaesemann]

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Manfred Koizar]

On Thu, 6 Nov 2003 00:50:06 +0900, Michael Glaesemann
<gr**@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...me-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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster