Update statement within select statement

I am trying to write a query in which I update a counter based on other conditions. For example:

with table 1 as (select *, count from table1)

select box_type, 
case when box_type = lag(box_type) over (order by time) 
then 
  count, update table1 set count = count + 1
else
  count
end as identifier

Here’s the basic jist of what I’m trying to do. I want a table that looks like this:

box_type	identifier
small	1
small	1
small	1
medium	2
medium	2
large	3
large	3
small	4

I just want to increment that identifier value every time the box_type changes

Thank you!

Answer

Your question only makes sense if you have a column that sepcifies the ordering. Let me assume such a column exists — based on your code, I’ll call it time.

Then, you can use lag() and a cumulative sum:

select t1.*,
       count(*) filter (where box_type is distinct from prev_box_type) over (order by time) as count
from (select t1.*,
             lag(box_type) over (order by time) as prev_box_type
      from table1 t1
     ) t1