Sql statement for tradeskill edit?

rencro · #1 09-08-2014, 05:35 PM

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 · #2 09-08-2014, 07:08 PM

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.

Code:

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 · #3 09-08-2014, 09:44 PM

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 · #4 09-09-2014, 07:50 PM

This should do what you need it to do:

Code:

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 · #5 09-09-2014, 07:55 PM

Wow! Thank you. I will try that.