[Release]QUERY-How To REMOVE a Completly Race Drop Degree

[Tommy.]

Hi Elitepvpers.
Im here to day to give you a query that can remove a completely race degree drop.

For Example, Removing a Degree 10 Euro

PHP Code:

USE SRO_VT_SHARD
DELETE _RefDropItemAssign
FROM _RefDropItemAssign Drops
LEFT join _RefObjCommon Objects
ON (Drops.RefItemID = Objects.ID) 
WHERE CodeName128 like '%ITEM_EU_%_10_%' 


'_10_' Depends on the Degree,
'_EU_' Depends on the Race,

P.S: Completely*

[Syloxx]

Hey, i wrote a slightly nicer query with the same function... i dont like it to use the CodeName for itemtype selection

DECLARE	@Type		BIT = 1, --(0 = Equip , 1 = Stones / Tablets)
		@Degree		TINYINT = 0, --(0 = all degrees),
		@Race		BIT = 0, --(0 = CH , 1 = EU)
		@MinClass	TINYINT,
		@MaxClass	TINYINT
		
		IF @Type = 0
			BEGIN
				IF @Degree = 0
					BEGIN
						SELECT		@MinClass = MIN(ROI.ItemClass),
									@MaxClass = MAX(ROI.ItemClass)
						FROM		_RefObjItem ROI
						INNER JOIN	_RefObjCommon ROC
						ON			ROI.ID = ROC.Link
						WHERE		ROC.TypeID1 = 3
						AND			ROC.TypeID2 = 1
					END
				ELSE
					BEGIN
						SELECT		@MinClass = @Degree * 3 - 2,
									@MaxClass = @Degree * 3
					END
				
				UPDATE		RDI
				SET			Service = 0
				FROM		_RefDropItemAssign RDI
				INNER JOIN	_RefObjCommon ROC
				ON			RDI.RefItemID = ROC.ID
				INNER JOIN	_RefObjItem ROI
				ON			ROC.Link = ROI.ID
				WHERE		ROC.TypeID1 = 3
				AND			ROC.TypeID2 = 1
				AND			ROC.Country = @Race
				AND			ROI.ItemClass BETWEEN @MinClass AND @MaxClass
			END
			
		IF @Type = 1
			BEGIN
				IF @Degree = 0
					BEGIN
						SELECT		@MinClass = MIN(ROI.ItemClass),
									@MaxClass = MAX(ROI.ItemClass)
						FROM		_RefObjItem ROI
						INNER JOIN	_RefObjCommon ROC
						ON			ROI.ID = ROC.Link
						WHERE		ROC.TypeID1 = 3
						AND			ROC.TypeID2 = 3
						AND			ROC.TypeID3 = 11
						AND			ROC.TypeID4 BETWEEN 1 AND 3
					END
				ELSE
					BEGIN
						SELECT		@MinClass = @Degree,
									@MaxClass = @Degree
					END
				
				UPDATE		RDI
				SET			Service = 0
				FROM		_RefDropItemAssign RDI
				INNER JOIN	_RefObjCommon ROC
				ON			RDI.RefItemID = ROC.ID
				INNER JOIN	_RefObjItem ROI
				ON			ROC.Link = ROI.ID
				WHERE		ROC.TypeID1 = 3
				AND			ROC.TypeID2 = 3
				AND			ROC.TypeID3 = 11
				AND			ROC.TypeID4 BETWEEN 1 AND 3
				AND			ROI.ItemClass BETWEEN @MinClass AND @MaxClass
			END

[Tommy.]

Originally Posted by Syloxx

Hey, i wrote a slightly nicer query with the same function... i dont like it to use the CodeName from itemtype selection

Great job dude. Welldone, But i'd prefer the codename selection for the beginners.

[Tazdingo7]

You should use an update instance instead of delete.
I mean service = 0

[asanoftw]

why so complicated set service 0

PHP Code:

update _RefDropItemAssign
SET Service = 0
from _RefDropItemAssign p
inner join _RefObjCommon a on a.ID = p.RefItemID
where a.CodeName128 like '%ITEM_EU_%_10_%' and p.Service = 0 


GS crash? re-back service 1

PHP Code:

update _RefDropItemAssign
SET Service = 1
from _RefDropItemAssign p
inner join _RefObjCommon a on a.ID = p.RefItemID
where a.CodeName128 like '%ITEM_EU_%_10_%' and p.Service = 1 


[Syloxx]

1st readd wont work
2nd where u can disable CH / EU only

[Tommy.]

Originally Posted by Tazdingo7

You should use an update instance instead of delete.
I mean service = 0

setting service = 0 causes a gs crash bro

[Royalblade*]

Don't need the "BEGIN" and "END" keywords there. Y U DO DIS?

[asanoftw]

Originally Posted by Syloxx

1st readd wont work
2nd where u can disable CH / EU only

PS: FIXED

[Syloxx]

Originally Posted by blablaRoyal

Don't need the "BEGIN" and "END" keywords there. Y U DO DIS?

true, begin / end is useless if there is only one subquery but its visually appealing and i just ask me why u didn't said:
-SELECT @MinClass and @MaxClass can be done with 1 selection
-IF @Degree = 0 ALL items will be disabled even pots because no TypeID set

anyway here is a fix for the Degree = 0 problem:

DECLARE	@Type		BIT = 1, --(0 = Equip , 1 = Stones / Tablets)
		@Degree		TINYINT = 0, --(0 = all degrees),
		@Race		BIT = 0, --(0 = CH , 1 = EU)
		@MinClass	TINYINT,
		@MaxClass	TINYINT
		
		IF @Type = 0
			BEGIN
				IF @Degree = 0
					BEGIN
						SELECT		@MinClass = MIN(ROI.ItemClass),
									@MaxClass = MAX(ROI.ItemClass)
						FROM		_RefObjItem ROI
						INNER JOIN	_RefObjCommon ROC
						ON			ROI.ID = ROC.Link
						WHERE		ROC.TypeID1 = 3
						AND			ROC.TypeID2 = 1
					END
				ELSE
					BEGIN
						SELECT		@MinClass = @Degree * 3 - 2,
									@MaxClass = @Degree * 3
					END
				
				UPDATE		RDI
				SET			Service = 0
				FROM		_RefDropItemAssign RDI
				INNER JOIN	_RefObjCommon ROC
				ON			RDI.RefItemID = ROC.ID
				INNER JOIN	_RefObjItem ROI
				ON			ROC.Link = ROI.ID
				WHERE		ROC.TypeID1 = 3
				AND			ROC.TypeID2 = 1
				AND			ROC.Country = @Race
				AND			ROI.ItemClass BETWEEN @MinClass AND @MaxClass
			END
			
		IF @Type = 1
			BEGIN
				IF @Degree = 0
					BEGIN
						SELECT		@MinClass = MIN(ROI.ItemClass),
									@MaxClass = MAX(ROI.ItemClass)
						FROM		_RefObjItem ROI
						INNER JOIN	_RefObjCommon ROC
						ON			ROI.ID = ROC.Link
						WHERE		ROC.TypeID1 = 3
						AND			ROC.TypeID2 = 3
						AND			ROC.TypeID3 = 11
						AND			ROC.TypeID4 BETWEEN 1 AND 3
					END
				ELSE
					BEGIN
						SELECT		@MinClass = @Degree,
									@MaxClass = @Degree
					END
				
				UPDATE		RDI
				SET			Service = 0
				FROM		_RefDropItemAssign RDI
				INNER JOIN	_RefObjCommon ROC
				ON			RDI.RefItemID = ROC.ID
				INNER JOIN	_RefObjItem ROI
				ON			ROC.Link = ROI.ID
				WHERE		ROC.TypeID1 = 3
				AND			ROC.TypeID2 = 3
				AND			ROC.TypeID3 = 11
				AND			ROC.TypeID4 BETWEEN 1 AND 3
				AND			ROI.ItemClass BETWEEN @MinClass AND @MaxClass
			END

[vietnguyen09]

Never works, when I remove or set Services = 0 for D9, D10, D12 then GS crashed