Re: update set from where... with count

"Jason Donald" <jason(at)sitepoint(dot)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