[Tiny Release] Mass Update Mob Items

11/17/2012 00:26 nephren#1

Hello epvpers,

i was bored and created a bit of "Mass Update Drops"

it's a simply SQL Update for MobItems. One More Tab 'mapID'
if you wanna update Map 1 all in one, you don't have to type everymobID.
Simple use:

Update xxx WHERE mapID = 1 AND ItemOrder = X

but be careful too: BackUP your current MobItems!
And if you wanna use this simple SQL Script just Rename your current
MobItems and use SELECT FROM renamed_mobDrops your Grades.

Notice 2: i don't take relics in it also i don't take CT/Haru/FM/MobGuards in it.
Some bosses like CloronTroll is map-stored too. So remind that if you wanna make Boss-Drops.

Thanks to Su1ph3r for the idea yesterday =)

Edit: 90% should be correct, maybe there are some mistakes in it please tell me if you find one =)
it's without EP5 Mobs because i don't have the ids here and can't download it at this moment.

EP 5 Mobs are in it but the Map-Row is "NULL"ed

[Only registered and activated users can see links. Click Here To Register...]

11/17/2012 02:58 bulgaria#2

Here is a script created by me 1 year ago.

PHP Code:


			
SELECT     c.Map1,c.MobID, c.MobName,  b.Grade,b.DropRate,b.ItemOrder 
FROM         PS_GameDefs.dbo.Mobs AS c inner join PS_GameDefs.dbo.MobItems as b ON c.MobID=b.MobID 
WHERE     (c.Map1=76 or c.Map2=76 or c.Map3=76) and b.Grade<>0 
ORDER BY b.Grade,b.DropRate DESC

04/26/2013 02:18 LeCanut#3

Oh, well, i have no idea about how to use this. I don't know if it's even for Shaiya ep 5. Thanks anyway.
T.

04/26/2013 07:53 n00bness#4

Not sure this is a really brilliant idea for drop updating. I mean, you made that query quite big and useless, creating a new table, without removing the old one. Anyone could have done it by simply opening the MobItems table in Design View and adding a new column, simple as fuck.
I myself have my own way of mass updating the drops, but your method is bad for those who update it via Shaiya Studio, as the query it generates inserts less 1 value than your new table requires.

I'm not saying it's bad, just making some constructive criticism, as usual.

04/26/2013 17:08 castor4878#5

It's (yet) an old thread and may be only used by a few, but ...

- the given SQL script "mobItems.sql" rises some remarks:

1) it contains 26181 insert statements (and 104745 lines) to more or less perform the following:

Code:

USE [PS_GameDefs]
GO

CREATE TABLE [dbo].[MobItemsNEW] (
	[RowID] [int] NOT NULL IDENTITY (1, 1),
	[MobID] [smallint] NOT NULL,
	[ItemOrder] [tinyint] NOT NULL,
	[Grade] [smallint] NOT NULL,
	[DropRate] [int] NOT NULL,
	[MapID] [smallint] NULL);
GO

DECLARE @cntMob int	-- the number of mobs to insert
DECLARE @mobID int	-- the ID of mob in loop
DECLARE @drop int	-- the ID of drop in loop

-- define the number of mobs to create
SET @cntMob  = 2908

-- let the base generates the right RowID identity value
SET IDENTITY_INSERT [dbo].[MobItemsNEW] OFF

SET @mobID = 0
WHILE (@mobID < @cntMob)
BEGIN
	-- insert 9 records matching 9 drop of current mob
	SET @drop = 1
	WHILE (@drop <= 9)
	BEGIN
		INSERT INTO [dbo].[MobItemsNEW] VALUES (@mobID, @drop, 0, 0, NULL)
		SET @drop = @drop +1
	END
	SET @mobID = @mobID + 1
END

even with comments, this version needs 36 lines.

so the remark: when you have / want to use code to generate a repetitive SQL script, use the same logic (code) in the SQL script to not have such repetitions.

2) the definition of an extra column 'mapID' to the table can indeed be helpful.
the column will not grant that all mobs are present in a single map (it's not its purpose) but will allow to do some select and update on the map criterion.
however, as indicated by n00bness, the right way to do it is to alter the current table, so something like:

Code:

ALTER TABLE [dbo].[MobItems] ADD [MapID] [smallint] DEFAULT NULL

3) as a matter of fact, and fact only, the scripts generated by shStudio (0.7.1) won't comply with the new table format.