How to do an update based on a count - SQL (postgres)

Question

I have a table, let's call it 'entries' that looks like this (simplified):

id [pk]
user_id [fk]
created [date]
processed [boolean, default false]

and I want to create an UPDATE query which will set the processed flag to true on all entries except for the latest 3 for each user (latest in terms of the created column). So, for the following entries:

1,456,2009-06-01,false
2,456,2009-05-01,false
3,456,2009-04-01,false
4,456,2009-03-01,false

Only entry 4 would have it's processed flag changed to true.

Anyone know how I can do this?

Answer 1

I don't know postgres, but this is standard SQL and may work for you.

update entries set
  processed = true
where (
  select count(*)
  from entries as E
  where E.user_id = entries.user_id
  and E.created > entries.created
) >= 3

In other words, update the processed column to true whenever there are three or more entries for the same user_id on later dates. I'm assuming the [created] column is unique for a given user_id. If not, you'll need an additional criterion to pin down what you mean as "latest".

In SQL Server you can do this, which is a little easier to follow and will probably be more efficiently executed:

with T(id, user_id, created, processed, rk) as (
  select
    id, user_id, created, processed,
    row_number() over (
      partition by user_id
      order by created desc, id
    )
  from entries
)
  update T set
    processed = true
  where rk > 3;

Updating a CTE is a non-standard feature, and not all database systems support row_number.