Sql statement for tradeskill edit?

[Mortow]

Is there a way to do a sql command to update all jewelcrafting recipes to use another container such as a collapsible jewelers kit (item# 17187)? I have been looking at the table tradeskill_recipe_entries and it looks like all the recipes are defined only by the recipe ID and not by tradeskill. Is this a possiblility or do I need to do them one at a time?

[dpjaf4t]

This should find ALL jewelcrafting recipes:

SELECT * from tradeskill_recipe_entries JOIN tradeskill_recipe ON tradeskill_recipe_entries.recipe_id=tradeskill_recipe.id WHERE tradeskill_recipe.tradeskill=68;

I would think you would want to insert item_id 17187 for any recipe where it is not already included, a good many of them seem to have it already.

[rencro]

To get the list of which recipes are not using that container just add:

AND iscontainer=1 AND item_id != 17187;

to the end of that query removing the previous ; (ninja edit about the semi colon)

Then your update will use similar conventions.

[Mortow]

Ok since I know just enough about sql to get myself into trouble, would I then use:

INSERT tradeskill_recipe_entries `item_id`=17187 WHERE tradeskill_recipe.tradeskill=68 AND iscontainer=1 AND item_id != 17187;

Thank you both for the quick replies.

[rencro]

No, unfortunately that wouldn't work. Also, the info i listed above is incorrect. Each recipe_id has multiple occurrences in the tradeskill_recipe_entries table so isolating one item was not the right solution.

Sorry...

[rencro]

There are 86 jewelry recipes not using item 17187. As long as you dont have any custom made ones this query should make the rest use that container. This goes with the proviso that you should have backups in case you need to revert an unsuccessful change.

INSERT INTO `tradeskill_recipe_entries` (`id`,`recipe_id`,`item_id`,`successcount`,`failcount`,`componentcount`,`salvagecount`,`iscontainer`) 
VALUES
('DEFAULT','7915','17187','0','0','0','0','1'),
('DEFAULT','7916','17187','0','0','0','0','1'),
('DEFAULT','7917','17187','0','0','0','0','1'),
('DEFAULT','7918','17187','0','0','0','0','1'),
('DEFAULT','7919','17187','0','0','0','0','1'),
('DEFAULT','7947','17187','0','0','0','0','1'),
('DEFAULT','7958','17187','0','0','0','0','1'),
('DEFAULT','7959','17187','0','0','0','0','1'),
('DEFAULT','8015','17187','0','0','0','0','1'),
('DEFAULT','8021','17187','0','0','0','0','1'),
('DEFAULT','8034','17187','0','0','0','0','1'),
('DEFAULT','8035','17187','0','0','0','0','1'),
('DEFAULT','8048','17187','0','0','0','0','1'),
('DEFAULT','8049','17187','0','0','0','0','1'),
('DEFAULT','8050','17187','0','0','0','0','1'),
('DEFAULT','8051','17187','0','0','0','0','1'),
('DEFAULT','8062','17187','0','0','0','0','1'),
('DEFAULT','8063','17187','0','0','0','0','1'),
('DEFAULT','8064','17187','0','0','0','0','1'),
('DEFAULT','8065','17187','0','0','0','0','1'),
('DEFAULT','8066','17187','0','0','0','0','1'),
('DEFAULT','8067','17187','0','0','0','0','1'),
('DEFAULT','8068','17187','0','0','0','0','1'),
('DEFAULT','8069','17187','0','0','0','0','1'),
('DEFAULT','8160','17187','0','0','0','0','1'),
('DEFAULT','8161','17187','0','0','0','0','1'),
('DEFAULT','8162','17187','0','0','0','0','1'),
('DEFAULT','8163','17187','0','0','0','0','1'),
('DEFAULT','8164','17187','0','0','0','0','1'),
('DEFAULT','8165','17187','0','0','0','0','1'),
('DEFAULT','8166','17187','0','0','0','0','1'),
('DEFAULT','8167','17187','0','0','0','0','1'),
('DEFAULT','8168','17187','0','0','0','0','1'),
('DEFAULT','8169','17187','0','0','0','0','1'),
('DEFAULT','8170','17187','0','0','0','0','1'),
('DEFAULT','8171','17187','0','0','0','0','1'),
('DEFAULT','8172','17187','0','0','0','0','1'),
('DEFAULT','8173','17187','0','0','0','0','1'),
('DEFAULT','8189','17187','0','0','0','0','1'),
('DEFAULT','8190','17187','0','0','0','0','1'),
('DEFAULT','8207','17187','0','0','0','0','1'),
('DEFAULT','8208','17187','0','0','0','0','1'),
('DEFAULT','8225','17187','0','0','0','0','1'),
('DEFAULT','8226','17187','0','0','0','0','1'),
('DEFAULT','8227','17187','0','0','0','0','1'),
('DEFAULT','8228','17187','0','0','0','0','1'),
('DEFAULT','8229','17187','0','0','0','0','1'),
('DEFAULT','8230','17187','0','0','0','0','1'),
('DEFAULT','8231','17187','0','0','0','0','1'),
('DEFAULT','8232','17187','0','0','0','0','1'),
('DEFAULT','8233','17187','0','0','0','0','1'),
('DEFAULT','8255','17187','0','0','0','0','1'),
('DEFAULT','8293','17187','0','0','0','0','1'),
('DEFAULT','8294','17187','0','0','0','0','1'),
('DEFAULT','8295','17187','0','0','0','0','1'),
('DEFAULT','8296','17187','0','0','0','0','1'),
('DEFAULT','8297','17187','0','0','0','0','1'),
('DEFAULT','8298','17187','0','0','0','0','1'),
('DEFAULT','8299','17187','0','0','0','0','1'),
('DEFAULT','8300','17187','0','0','0','0','1'),
('DEFAULT','8301','17187','0','0','0','0','1'),
('DEFAULT','8302','17187','0','0','0','0','1'),
('DEFAULT','8303','17187','0','0','0','0','1'),
('DEFAULT','8304','17187','0','0','0','0','1'),
('DEFAULT','8305','17187','0','0','0','0','1'),
('DEFAULT','8306','17187','0','0','0','0','1'),
('DEFAULT','8351','17187','0','0','0','0','1'),
('DEFAULT','8396','17187','0','0','0','0','1'),
('DEFAULT','8397','17187','0','0','0','0','1'),
('DEFAULT','8414','17187','0','0','0','0','1'),
('DEFAULT','8415','17187','0','0','0','0','1'),
('DEFAULT','8416','17187','0','0','0','0','1'),
('DEFAULT','8417','17187','0','0','0','0','1'),
('DEFAULT','8421','17187','0','0','0','0','1'),
('DEFAULT','8434','17187','0','0','0','0','1'),
('DEFAULT','8435','17187','0','0','0','0','1'),
('DEFAULT','8436','17187','0','0','0','0','1'),
('DEFAULT','8437','17187','0','0','0','0','1'),
('DEFAULT','8438','17187','0','0','0','0','1'),
('DEFAULT','8439','17187','0','0','0','0','1'),
('DEFAULT','8440','17187','0','0','0','0','1'),
('DEFAULT','8441','17187','0','0','0','0','1'),
('DEFAULT','8442','17187','0','0','0','0','1'),
('DEFAULT','8443','17187','0','0','0','0','1'),
('DEFAULT','10490','17187','0','0','0','0','1'),
('DEFAULT','10929','17187','0','0','0','0','1');

Sorry for the earlier confusion

I created this list by exporting the above querries and used excel to sort because of the recipe_id issue.

Perhaps there's an easier way to do this without having to resort to exporting and excel, but i cant figure it out at the moment...

[Mortow]

I really appreciate all the effort you put into this but unfortunately I have a ton of custom recipes that I didn't put the collapsible containers on. Jewelcrafting is just one of the tradeskills. I was hoping it would be a simple thing that I could adapt to all the different tradeskills. Thank you very much for your time.

[Shendare]

This should do what you need it to do:

INSERT INTO tradeskill_recipe_entries (recipe_id, item_id, successcount, failcount, componentcount, salvagecount, iscontainer) SELECT r.id, 17187, 0, 0, 0, 0, 1 FROM tradeskill_recipe r WHERE r.tradeskill=68 AND r.id NOT IN (SELECT recipe_id FROM tradeskill_recipe_entries e WHERE e.item_id=17187);

[Mortow]

Wow! Thank you. I will try that.

[Shendare]

You bet!

Though I got in a hurry to leave the office and forgot to break down what it does so you can be sure before you run it.

If you read it logically right-to-left:

1. ... (SELECT recipe_id FROM tradeskill_recipe_entries e WHERE e.item_id=17187)

Tells the database to make a list of all recipe_ids that work with container item # 17187, which an earlier post said was your desired container)

2. SELECT r.id, 17187, 0, 0, 0, 0, 1 FROM tradeskill_recipe r WHERE r.tradeskill=68 AND r.id NOT IN ...

Tells the database to build a set of recipe_entry records containing the recipe.id, container_id, and proper counts & flags for all recipes in the tradeskill_recipe table that are Jewelcrafting recipes (an earlier post said it was type 68) but are NOT in the list of recipes from #1 above that already work with container # 17187.

3. INSERT INTO tradeskill_recipe_entries (recipe_id, item_id, successcount, failcount, componentcount, salvagecount, iscontainer)

Tells the database to insert those newly built records into the tradeskill_recipe_entries table.

All together, the query creates a new tradeskill_recipe_entries record with containerid 17187 for each Jewelcrafting recipe it finds that does not already have a recipe_entries record for item 17187.

[Mortow]

Nice! I really appreciate the break down on that. That helps me understand a little more so I can modify it to other containers for other trade skills. Thank you very much.