|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Support::Windows Servers Support forum for Windows EQEMu users. |
09-08-2014, 12:02 PM
|
|
Hill Giant
|
|
Join Date: Apr 2013
Posts: 215
|
|
Sql statement for tradeskill edit?
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?
|
09-08-2014, 12:28 PM
|
Fire Beetle
|
|
Join Date: May 2010
Posts: 21
|
|
This should find ALL jewelcrafting recipes:
Code:
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.
|
09-08-2014, 01:13 PM
|
Hill Giant
|
|
Join Date: Sep 2008
Location: So. California
Posts: 219
|
|
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.
Last edited by rencro; 09-08-2014 at 01:20 PM..
Reason: clarified semi colon use
|
09-08-2014, 01:26 PM
|
|
Hill Giant
|
|
Join Date: Apr 2013
Posts: 215
|
|
Ok since I know just enough about sql to get myself into trouble, would I then use:
Code:
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.
|
09-08-2014, 05:35 PM
|
Hill Giant
|
|
Join Date: Sep 2008
Location: So. California
Posts: 219
|
|
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...
|
|
|
|
09-08-2014, 07:08 PM
|
Hill Giant
|
|
Join Date: Sep 2008
Location: So. California
Posts: 219
|
|
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...
|
|
|
|
|
|
|
09-09-2014, 08:21 PM
|
Dragon
|
|
Join Date: Apr 2009
Location: California
Posts: 814
|
|
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.
Last edited by Shendare; 09-09-2014 at 08:24 PM..
Reason: unintended smiley
|
|
|
|
09-09-2014, 09:03 PM
|
|
Hill Giant
|
|
Join Date: Apr 2013
Posts: 215
|
|
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.
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT -4. The time now is 09:13 PM.
|
|
|
|
|
|
|
|
|
|
|
|
|