MySQL Update current record in table -

i've been trying come elegant way following, let's have following table:

+-----+---------+------------+--------------+------------+------------+--------+ | id  | empnum  |    name    |    title     | startdate  | updatedate | iscurr | +-----+---------+------------+--------------+------------+------------+--------+ |   1 | 0000001 | john smith | engineer     | 01/01/2017 | 01/02/2017 | n      | |   2 | 0000001 | john smith | sr. engineer | 01/01/2017 | null       | y      | +-----+---------+------------+--------------+------------+------------+--------+ 

when title of employee changes, want update updatedate of current employee record , change iscurr n, insert new record current one. example if employee john smith got promoted team lead on 01/03/2017, resulting table this.

+-----+---------+------------+--------------+------------+------------+----

----+ | id  | empnum  |    name    |    title     | startdate  | updatedate | iscurr | +-----+---------+------------+--------------+------------+------------+--------+ |   1 | 0000001 | john smith | engineer     | 01/01/2017 | 01/02/2017 | n      | |   2 | 0000001 | john smith | sr. engineer | 01/01/2017 | 01/03/2017 | n      | |   3 | 0000001 | john smith | team lead    | 01/01/2017 | null       | y      | +-----+---------+------------+--------------+------------+------------+--------+ 

from can tell mysql not have merge function need in 2 separate commands. in update recent record , insert new row. there better way in mysql?

thanks in advance.

you try arrange desired query insert ... on duplicate key. need id index. available since mysql version 5.5.

insert table (id, empnum, name, title, startdate, updatedate, iscurr)  values (3, '0000001', 'john smith', 'team lead', '01/01/2017', null, 'y'), (2, '0000001', 'john smith', 'sr. engineer', '01/01/2017', '01/03/2017', 'n') on duplicate key update iscurr='n', updatedate='01/03/2017';