MySQL event scheduling update query

Question

I am just getting to grips with the event scheduler in MySQL and I am writing a query to update a column on the same date each year.

The problem I am having is working out the best way to structure my update query.

Every user has a value in column_x that is a varchar field in the format of int/int. These values range from 7/1 to 7/11, 8/1 to 8/11, 9/1 to 9/11 etc

When the event is run, I would like to increment the first int by 1 but keep the number after the forward-slash the same. For example, 7/2 becomes 8/2, 8/2 becomes 9/2 etc

Does anyone have a good suggestion as to how to create an update statement that would achieve this?

Thanks.

Answer 1

I assumed that the column you store your values is VARCHAR, so here is solution:

UPDATE table SET value = CONCAT_WS( '/', (SUBSTRING_INDEX(value,'/',1)+1), SUBSTRING_INDEX(value,'/',-1) )

This will change the:
1/7 -> 2/7
1/8 -> 2/8
1/9 -> 2/9
1/10 -> 2/10
1/11 -> 2/11