Sql statement for tradeskill edit?

[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);