EQEmulator Forums - Help with code to remove a list of loot drops.

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Support::General Support (https://www.eqemulator.org/forums/forumdisplay.php?f=598)

- - Help with code to remove a list of loot drops. (https://www.eqemulator.org/forums/showthread.php?t=42584)

jaspen

08-27-2019 12:11 PM

Help with code to remove a list of loot drops.

I am looking for a way to remove a large list of items from the lootdrop tables so each time I update the database, I can run this script/command. An example is, currently, non-visible defiant gear was left in the loot drops (ie. Combatant's and Adept's), while visible gear was removed and added to global loot (ie. Crude Defiant, Simple Defiant, etc.). I want to finish removing those and will add those manually to global as well.

I read around the forums, and found a way to search for a word or phrase, it would list all matching items in Heidi and in turn, I would have to manually select and delete all offending items. If possible, I would like it to automatically delete all from a list I create.

Is there a reasonable way of doing this?

chrsschb

08-27-2019 01:48 PM

Code:

DELETE FROM lootdrop_entries

WHERE item_id in (itemid1, itemid2, itemid3, ...);

I believe that will do it. Just add to the list as you get more items.

jaspen

08-27-2019 01:59 PM

Thanks, I wasn't expecting so simple. Had I wrote it I would have used =. Is there an easy explanation for why you use in for this situation? Still learning...

jaspen

08-27-2019 02:22 PM

The in refers to the stuff in the ( )... For some reason I was expecting a deeper meaning but it was simply straightforward.

Uleat

08-27-2019 07:44 PM

You can even put another query inside the where clause parentheses :)

Just make sure the query returns a list of single field results.

jaspen

08-28-2019 10:41 AM

Quote:

Originally Posted by Uleat (Post 263327)

You can even put another query inside the where clause parentheses :)

Just make sure the query returns a list of single field results.

Could you give a simple example?

Uleat

08-28-2019 05:46 PM

Code:

DELETE FROM `lootdrop_entries`

WHERE `item_id` IN (

  SELECT `id` FROM `items` WHERE `Name` LIKE 'Ornate Defiant%' 

);

With something like that, you can tailor the inner query to your desires before you ever run the outer one.

Turmoiltoad

08-29-2019 08:17 AM

Also, if it's something you think you might want to undo someday...make use of "disabled_chance" field instead of deleting them all:

Code:

UPDATE lootdrop_entries

SET disabled_chance = chance, chance = 0

WHERE item_id (...follow Uleat's example)

All times are GMT -4. The time now is 12:31 PM.