Как правильно составить запрос mysql?

Question

[AcidZer0]

Нужно сформулировать запрос. Сперва в таблице hlstatsx.hlstats_PlayerUniqueIds нужно выбрать все строки, содержащие в поле game значение tf, а так же не подходящие под маску '[U:%'
Затем нужно прочить в выбранных строках значения столбца playerid. Далее нужно найти по всей таблице hlstatsx совпадения по этому полю, а так же полям killerId и victimId и удалить их вместе с теми, что уже выбрали. Потом вернуться в hlstatsx.hlstats_PlayerUniqueIds, и преобразовать значения поля uniqueid по следующему принципу: значение поля состоит из [U:xxxyyy], сперва отбросим лишнее, останется xxxyyy(значение цифровое), его делим на два и отбрасываем дробную часть. C mysql никогда не сталкивался, даже примерно не знаю, с какого края подойти.

Answer 1

[AcidZer0]

USE hlstatsx;

CREATE TABLE tmp_PlayerIds (playerId INT);

INSERT INTO tmp_PlayerIds
SELECT playerId
FROM hlstats_PlayerUniqueIds
WHERE game = 'tf'
AND uniqueId NOT LIKE '[U:%';

DELETE FROM hlstats_Players WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_PlayerNames WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_PlayerUniqueIds WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Players_Awards WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Players_History WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Players_Ribbons WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);

DELETE FROM hlstats_Events_ChangeName WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_ChangeTeam WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_Connects WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_Disconnects WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_Frags WHERE killerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_Frags WHERE victimId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_Latency WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_Entries WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_PlayerActions WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_Suicides WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_TeamBonuses WHERE playerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_Teamkills WHERE killerId IN (SELECT playerId FROM tmp_PlayerIds);
DELETE FROM hlstats_Events_Teamkills WHERE victimId IN (SELECT playerId FROM tmp_PlayerIds);

DROP TABLE tmp_PlayerIds;

UPDATE hlstats_PlayerUniqueIds
SET uniqueId = concat(MOD(SUBSTRING(uniqueId,6,9),2), ':', FLOOR(SUBSTRING(uniqueId,6,9)/2))

Помог товарищ погромист.