Update first occurrence of value in a time interval

Question

I'm trying to set the value of another column on the first occurrence of any value in a username column in monthly intervals, if there's another column with an specific value.

create table table1
(
    username varchar(30) not null,
    `date` date not null,
    eventid int not null,
    firstFlag int null
);

insert table1 (username,`date`, eventid) values 
('john','2015-01-01', 1)
, ('kim','2015-01-01', 1)
, ('john','2015-01-01', 1)
, ('john','2015-01-01', 1)
, ('john','2015-03-01', 2)
, ('john','2015-03-01', 1)
, ('kim','2015-01-01', 1)
, ('kim','2015-02-01', 1);

This should result in:

| username |       date | eventid | firstFlag |
|----------|------------|---------|-----------|
|     john | 2015-01-01 |       1 |         1 |
|      kim | 2015-01-01 |       1 |         1 |
|     john | 2015-01-01 |       1 |    (null) |
|     john | 2015-01-01 |       1 |    (null) |
|     john | 2015-03-01 |       2 |         1 |
|     john | 2015-03-01 |       1 |    (null) |
|      kim | 2015-01-01 |       1 |    (null) |
|      kim | 2015-02-01 |       1 |         1 |

I've tried using joins as described here, but it updates all rows:

update table1 t1
inner join 
(   select username,min(`date`) as minForGroup
    from table1
    group by username,`date`
) inr
on inr.username=t1.username and inr.minForGroup=t1.`date`
set firstFlag=1;

The approach you refer to doesn't work in your case because you don't have any unique constraint . You have to add some unique index or primary key to do what you need. Alternatives would be to use internal row identifier or updatable cursors; unfortunately neither of them is implemented in Mysql (at least up to 5.7) . — a1ex07, Feb 23 '18 at 20:36

score 1 · Accepted Answer · answered Feb 23 '18 at 20:59

As a1ex07 points out, it would need another per row unique constrain to update the rows I need to:

update table1 t1
inner join 
(   select id, username,min(`date`) as minForGroup
    from table1
    where eventid = 1
    group by username,month(`date`)
) inr
on inr.id=t1.id and inr.username=t1.username and inr.minForGroup=t1.`date`
set firstFlag=1;

Add an Id column, and use it on the join on constrains.

To allow only those that satisfies a specific condition on another column you need the where clause inside the subquery, otherwise it would try to match different rows as the subquery would return rows with eventid=2 while the update query would return only those with eventid=1.

To use yearly intervals instead of monthly, change the group by statement to use years.

Update first occurrence of value in a time interval

1 Answers1