Запрос привязки клиентов при PPPoE

[Numenor]

Прошу помочь в написании запроса для внесения mac адресов из dhs_sessions в таблицу ip_groups.
UPDATE `ip_groups` SET `allowed_cid` =IFNULL(SELECT `Calling_Station_Id` FROM `dhs_sessions_log` WHERE `User_Name` = `uname ORDER BY `id` DESC LIMIT 0 , 1) WHERE `is_deleted` =0;

[xxxupg]

поддерживаю вопрос!
интересует добавить всем привязку из последнего использованного МАКа

[xxxupg]

значит нет ни у кого подобного скрипта?

[Numenor]

Создаем иднексы

Код: Выделить всё

create index ip_groups_uname on ip_groups(uname);
create index dhs_sessions_log_user_name_date on dhs_sessions_log(user_name,last_update_date);

Выборка

Код: Выделить всё

select uname, cid from (select uname,cid,date, (select max(d3.last_update_date) from dhs_sessions_log as d3 where d3.user_name=uname ) as datemax from (select d1.user_name as uname,d1.calling_station_id as cid,max(d1.last_update_date) as date from dhs_sessions_log as d1 group by d1.user_name,d1.calling_station_id) as d2) as md where md.date=md.datemax;

Сам запрос

Код: Выделить всё

update ip_groups as ip0, (select uname, cid from (select uname,cid,date, (select max(d3.last_update_date) from dhs_sessions_log as d3 where d3.user_name=uname ) as datemax from (select d1.user_name as uname,d1.calling_station_id as cid,max(d1.last_update_date) as date from dhs_sessions_log as d1 group by d1.user_name,d1.calling_station_id) as d2) as md where md.date=md.datemax) as cid0 set ip0.allowed_cid=cid0.cid where ip0.allowed_cid='' and ip0.is_deleted=0 and ip0.uname=cid0.uname;