SQLTeam.com | Weblogs | Forums

Update a table with with similar rows whose times are within 30 seconds


#1

I've got this table:

prod | customer |   city  | num  |       time         | isextra
-----+----------+---------+------+--------------------+-------
 1   | Jim      |  Venice |  5   |2015-08-27 1:10:00  | 0
 1   | Jim      |  Venice |  5   |2015-08-27 1:10:15  | 0
 1   | Jim      |  Venice |  5   |2015-08-27 1:10:28  | 0
 4   | Jane     |  Vienna |  8   |2018-06-04 2:20:43  | 0
 4   | Jane     |  Vienna |  8   |2018-06-04 2:20:43  | 0
 4   | Jane     |  Vienna |  8   |2018-06-04 2:20:49  | 0
 4   | Jane     |  Vienna |  8   |2018-06-04 2:30:55  | 0
 7   | Jack     | Vilnius |  4   |2015-09-15 2:20:55  | 0
 7   | Jake     |   Vigo  |  9   |2018-01-01 10:20:05 | 0
 7   | Jake     |   Vigo  |  2   |2018-01-01 10:20:25 | 0

Now take all rows that are similar by prod, customer, city, num then any row whose time is within 30 seconds of the first in the group, its 'isextra' field is UPDATED to 1

I'd like to use a CTE query with an UPDATE and have this be the result:

prod | customer |   city  | num  |       time         | isextra
-----+----------+---------+------+--------------------+-------
 1   | Jim      |  Venice |  5   |2015-08-27 1:10:00  | 0
 1   | Jim      |  Venice |  5   |2015-08-27 1:10:15  | 1
 1   | Jim      |  Venice |  5   |2015-08-27 1:10:28  | 1
 4   | Jane     |  Vienna |  8   |2018-06-04 2:20:43  | 0
 4   | Jane     |  Vienna |  8   |2018-06-04 2:20:43  | 1
 4   | Jane     |  Vienna |  8   |2018-06-04 2:20:49  | 1
 4   | Jane     |  Vienna |  8   |2018-06-04 2:30:55  | 0
 7   | Jack     | Vilnius |  4   |2015-09-15 2:20:55  | 0
 7   | Jake     |   Vigo  |  9   |2018-01-01 10:20:05 | 0
 7   | Jake     |   Vigo  |  2   |2018-01-01 10:20:25 | 0

Ok, I have this working, but how to convert to CTE? Or is it even worth it, would it offer any performance benefits over millions of rows?

update public.mytable
  set isextra = 1 
  from 
  (
      select prod, customer, city, num, min(time) as mintime
      from mytable 
      group by prod, customer, city, num
  ) b
  where public.mytable.prod = b.prod
    and public.mytable.customer = b.customer
    and public.mytable.city = b.city
    and public.mytable.num = b.num
    and public.mytable.time <= b.mintime + interval '30 seconds'
   and public.mytable.time <> b.mintime;