From: | "Jason Donald" <jason(at)sitepoint(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | update set from where... with count |
Date: | 2001-09-06 06:16:05 |
Message-ID: | 3B97A145.18376.598D8E1@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2001-09-06 06:24:19 | Re: FOREIGN KEY: MATCH FULL |
Previous Message | Oliver Elphick | 2001-09-06 05:40:43 | Re: CREATE USER vs. createuser |