Help with code to remove a list of loot drops.

[jaspen]

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]

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]

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]

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

[Uleat]

You can even put another query inside the where clause parentheses

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

[jaspen]

Originally Posted by Uleat

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]

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]

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

UPDATE lootdrop_entries
SET disabled_chance = chance, chance = 0
WHERE item_id (...follow Uleat's example)