[Level 3] Rownum/auto_increment, substring, reverse index, case and mutli-table update for MySQL

-- [rownum]
-- set initial equipment_id
select max(equipment_id) from device into @rownum;  
-- insert into equipment
insert into device
select @rownum:=ifnull(@rownum,-1)+1

from device;

-- [multi-table update]
update event
inner join device using (deviceid)
inner join equipment on device.macaddress = equipment.mac_address
set event.equipment_id = equipment.equipment_id
where event.eventtypeid < 400
;

-- [reverse index]
select instr(c,','),
substring(c,1,instr(c,',')-1),
reverse(substring(c,1,instr(c,',')-1)),
reverse(substring(reverse(substring(c,1,instr(c,',')-1)),1,instr(reverse(substring(c,1,instr(c,',')-1)),'@')-1))
from
(
select 'a@1:2:3:4,x,y,z' as c
union all
select '1:2:3:4,x,y,z' as c
union all
select 'a@b@1:2:3:4,x,y,z' as c
) as t

-- [case]
update types

set id =
  ( select case types.id
when 1 then 10001
when 2 then 10002
when 3 then 10003
when 4 then 10004
else types.id
end
)

Wish this helps. regards, Stanley Huang