update set from where... with count - Mailing list pgsql-general

From Jason Donald
Subject update set from where... with count
Date
Msg-id 3B97A145.18376.598D8E1@localhost
Whole thread Raw
Responses Re: update set from where... with count
List pgsql-general
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.



pgsql-general by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: embedded SQL cursos declare fails
Next
From: Mike Withers
Date:
Subject: problem with selects based on dates