Thread: update set from where... with count

Hi.  I am having trouble getting the following to work.  Please find a
complete cut-paste-run example below between the lines that
demonstrates the problem.

_______________________________________________________

CREATE TABLE items (
    recdate        DATE,
    item        TEXT
);

CREATE TABLE summary (
    recdate        DATE,
    item        TEXT,
    hits        INTEGER
);

INSERT INTO items VALUES   ('1-1-2000', 'widget');
INSERT INTO items VALUES   ('1-1-2000', 'widget');
INSERT INTO items VALUES   ('1-1-2000', 'widget');
INSERT INTO summary VALUES ('1-1-2000', 'widget', 0);

UPDATE
 summary
SET
 hits = s.hits + 1
FROM
 summary AS s,
 items   AS i
WHERE
 s.recdate = i.recdate  AND
 s.item    = i.item;

SELECT
 recdate, item, hits
FROM
 summary;
_______________________________________________________


It outputs:

  recdate   |  item  | hits
------------+--------+------
 2000-01-01 | widget |    1
(1 row)

But what I would rather it have shown is:

  recdate   |  item  | hits
------------+--------+------
 2000-01-01 | widget |    3
(1 row)

...because I am trying to update each widget's number of instances
for each day.

After playing around with this for a long time, trying to use
variations of count, grouping etc, I can't work out how to do this
efficiently.

There are several thousand widgets with several thousand
instances, each day.  I have many differently structured widget
tables, the above is just an example.  I would rather not add
lengthy trigger statements to each if I can avoid it with a single
neat solution, or a neat rule or something.

Do you have any suggestions for the best way to tackle this
problem?  Can it be done with normal SQL or will I have to write a
PLSQL function to do it?

Thanks in advance for any light you can shed.

Jason.

"Jason Donald" <jason@sitepoint.com> writes:
> UPDATE
>  summary
> SET
>  hits = s.hits + 1
> FROM
>  summary AS s,
>  items   AS i
> WHERE
>  s.recdate = i.recdate  AND
>  s.item    = i.item;

This is almost certainly *not* what you want to do.  What the above
query requests is a three-way join between the target table (summary),
summary AS s, and items AS i.  Since there isn't any constraint on
the target table, what will effectively happen is that every row in
summary gets incremented --- and would get incremented more than once,
were it not for some rather arcane visibility rules that prevent a
given target row from being updated more than once in a single UPDATE.
In any case, the update is being driven off the value of hits from the
first s row, which might not have anything to do with the current target
row.

I think what you really need here is a sub-select, on the order of

UPDATE summary
SET hits = hits + (SELECT count(*) FROM items as i
                   WHERE summary.recdate = i.recdate AND
                         summary.item = i.item);

            regards, tom lane