update set from where... with count

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.

Responses

Browse pgsql-general by date

  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