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

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

From
Jeff Kowalczyk
Date:
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)

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

From
Jeff Kowalczyk
Date:
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]-------------------------------------------------------


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

From
Manfred Koizar
Date:
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

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

From
Michael Glaesemann
Date:
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


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

From
Manfred Koizar
Date:
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

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

From
Michael Glaesemann
Date:
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


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

From
Manfred Koizar
Date:
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