Need SQL guru help [Archive] - EQEmulator Forums

Shin Noir

01-23-2016, 02:43 AM

This may be too verbose for your use case, but may be what you really imply, and could be applied for your use case by just doing the lootdrop entries query instead of all of them, never the less:

If you wanted to remove all "instances" of an item from your DB that a player could have access to, but not the item itself (so it's available via GM only #summonitem), here's something i've been working on.. (Note: This is not heavily tested, always back up!)

Replace any ? with the itemid you wish to delete.
There's STILL some entries that will still be there, but they are mainly used for backup or logging tables that a player's access to said item will not be available..

DELETE FROM inventory i WHERE i.itemid = ?;
DELETE FROM lootdrop_entries le WHERE le.item_id = ?;
DELETE FROM merchantlist ml WHERE ml.item_id = ?;
DELETE FROM merchantlist_temp ml WHERE ml.item_id = ?;
DELETE FROM guild_bank gb WHERE gb.itemid = ?;
DELETE FROM ground_spawns gs WHERE gs.item = ?;
DELETE FROM tradeskill_recipe tr WHERE tr.id IN (SELECT recipe_id FROM tradeskill_recipe_entries tre WHERE tre.item_id = ?);
DELETE FROM sharedbank sb WHERE sb.itemid = ?;
DELETE FROM forage f WHERE f.itemid = ?;
DELETE FROM merc_inventory mi WHERE mi.item_id = ?;
DELETE FROM tradeskill_recipe_entries tre WHERE tre.item_id = ?;
DELETE FROM buyer b WHERE b.itemid = ?;
DELETE FROM character_bandolier cb WHERE cb.item_id = ?;
DELETE FROM character_corpse_items cci WHERE cci.item_id = ?;
DELETE FROM character_pet_inventory cpi WHERE cpi.item_id = ?;
DELETE FROM character_potionbelt cp WHERE cp.item_id = ?;
DELETE FROM fishing f WHERE f.itemid = ?;
DELETE FROM item_tick it WHERE it.it_itemid = ?;
DELETE FROM keyring k WHERE k.item_id = ?;
DELETE FROM object_contents oc WHERE oc.itemid = ?;
DELETE FROM pet_equipmentset_entries pee WHERE pee.item_id = ?;
DELETE FROM starting_items si WHERE si.itemid = ?;
DELETE FROM tasks t WHERE t.rewardid = ?;
DELETE FROM titles t WHERE t.item_id = ?;
DELETE FROM trader t WHERE t.item_id = ?;
DELETE FROM veteran_reward_templates vrt WHERE vrt.item_id = ?;

to build upon this, you could get your itemids (since it seems you want multiples) by a query like:
SELECT * FROM items WHERE NAME LIKE '%sacred%symbol%' OR NAME LIKE '%eminent%symbol%';
Review this result set, and ensure all item ids are valid.
My dump shows like.. 426 items. Here's a pack I found (again, though, I recommend you learn how to get an item id list and not just take my quote)

(34989, 34990, 34991, 34992, 34993, 34994, 47402, 47403, 47404, 47405, 47406, 47407, 47408, 47409, 47410, 47411, 47412, 47413, 47414, 47415, 47416, 47417, 47418, 47419, 47420, 47421, 47422, 47423, 47424, 47425, 47426, 47427, 47428, 47429, 47430, 47431, 47432, 47433, 47434, 47435, 47436, 47437, 47438, 47439, 47440, 47441, 47442, 47443, 48577, 48578, 48579, 48580, 48581, 48582, 48583, 48584, 48585, 48586, 48587, 48588, 48589, 48590, 48591, 48592, 48593, 48594, 48595, 48596, 48597, 48598, 48599, 48600, 48601, 48602, 48603, 48604, 48605, 48606, 48607, 48608, 48609, 48610, 48611, 48612, 48613, 48614, 48615, 48616, 48617, 48618, 48619, 48620, 48621, 48622, 48623, 48624, 48625, 48626, 48627, 48628, 48629, 48630, 48631, 48632, 48633, 48634, 48635, 48636, 48637, 48638, 48639, 48640, 48641, 48642, 48643, 48644, 48645, 48646, 48647, 48648, 48649, 48650, 48651, 48652, 48653, 48654, 48655, 48656, 48657, 48658, 48659, 48660, 48661, 48662, 48663, 48664, 48665, 48666, 48667, 48668, 48669, 48670, 48671, 48672, 48673, 48674, 48675, 48676, 48677, 48678, 48679, 48680, 48681, 48682, 48683, 48684, 48685, 48686, 48687, 48688, 48689, 48690, 48691, 48692, 48693, 48694, 48695, 48696, 48697, 48698, 48699, 48700, 48701, 48702, 92234, 92235, 92238, 92239, 92242, 92243, 92246, 92247, 92250, 92251, 92254, 92255, 92258, 92259, 92498, 92499, 92500, 92501, 92502, 92503, 92504, 92505, 92506, 92507, 92508, 92509, 92510, 92511, 92512, 92513, 92514, 92515, 92516, 92517, 92518, 92519, 92520, 92521, 92522, 92523, 92524, 92525, 92526, 92527, 92528, 92529, 92530, 92531, 92532, 92533, 92534, 92535, 92536, 92537, 92538, 92539, 92540, 92541, 92542, 92543, 92544, 92545, 92546, 92547, 92548, 92549, 92550, 92551, 92552, 92553, 92554, 92555, 92556, 92557, 92558, 92559, 92560, 92561, 92562, 92563, 92564, 92565, 92566, 92567, 92568, 92569, 92570, 92571, 92572, 92573, 92574, 92575, 92576, 92577, 92578, 92579, 92580, 92581, 92582, 92583, 92584, 92585, 92586, 92587, 92588, 92589, 92590, 92591, 92592, 92593, 92594, 92595, 92596, 92597, 92598, 92599, 92600, 92601, 92602, 92603, 92604, 92605, 92606, 92607, 92608, 92609, 92610, 92611, 92612, 92613, 92614, 92615, 92616, 92617, 92618, 92619, 92620, 92621, 92622, 92623, 92624, 92625, 92626, 92627, 92628, 92629, 92630, 92631, 92632, 92633, 92634, 92635, 92636, 92637, 92638, 92639, 92640, 92641, 92642, 92643, 92644, 92645, 92646, 92647, 92648, 92649, 92650, 92651, 92652, 92653, 92654, 92655, 92656, 92657, 92658, 92659, 92660, 92661, 92662, 92663, 92664, 92665, 92666, 92667, 92668, 92669, 92670, 92671, 92672, 92673, 92674, 92675, 92676, 92677, 92678, 92679, 92680, 92681, 92682, 92683, 92684, 92685, 92686, 92687, 92688, 92689, 92690, 92691, 92692, 92693, 92694, 92695, 92696, 92697, 92698, 92699, 92700, 92701, 92702, 92703, 92704, 92705, 92706, 92707, 92708, 92709, 92710, 92711, 92712, 92713, 92714, 92715, 92716, 92717, 92718, 92719, 92720, 92721, 92722, 92723, 92724, 92725, 92726, 92727, 92728, 92729, 92730, 92731, 92732, 92733, 92734, 92735)

You could take the above array list, and go to my original delete queries, search, replace all instances of "= ?" with "IN (###, ###)" and it do a delete matching any of those item ids.