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.
