Server Wipe

07/05/2011 22:14 TheSyn#1
Was just wondering been looking around on here and been only able to find how to do a DB backup was wondering if anyone could post here let me know how to do a server wipe of all items and such on the accounts on the DB do i have to go through them individually and clear everything or was there a easyer way any advice would be appreciated
07/06/2011 00:01 [ADM]Wind#2
Use any of these :

Code:
USE PS_GameData
DELETE FROM CharItems WHERE ItemID = 'ItemID';
Code:
USE PS_GameData
DELETE FROM UserStoredItems WHERE UserUID = 'UserUID';
Code:
USE PS_GameData
DELETE FROM Chars WHERE UserUID = 'UserUID';
07/06/2011 06:23 TheSyn#3
Ok So i just go under any of those and put 0 or null in each field and is CharItems what they have on and userstoreditems what is in there WH and does chars UderUID does that delete the character not really sure another question if i wanna delete a account do i just fill everything in with nulls again or is there a procedure to follow? and thks for the reply Wind :)
07/07/2011 16:42 Danco1990#4
To delete the WHOLE table ( danger i may add) you can use a truncate. I have not yet found a way to do this over a full database, but if you make a quiry on, for example, ps_gamelog, you can do it like this:

TRUNCATE TABLE Gamelog
TRUNCATE TABLE Userlog

I do this each week to keep our server quick, since these 2 tabels eat alot of memory. When you do a truncate make sure to shrink! My normal gamelog is about 6 GB each week, after a truncate + shrink its only around 2 MB. Shrinking is easy, rightclick your database (not table) and look through the options, i choose to shrink the database, not the files, basic settings are enough, do not change unless you know what you are doing.

This will empty the WHOLE table. you cannot free up an autonumbering on, for example, users_master. You can delete the values, but the empty spaces will remain untouched. That you can indeed do with the DELETE FROM commands. Always provide a where clause, otherwise your whole table is empty.
07/07/2011 21:34 TheSyn#5
Ahh ok thks Danco xD
07/08/2011 17:33 ShaiyaOmega#6
there is already a Procedure for this.

Code:
--Procedure: dbo.usp_Clear_Table

CREATE PROCEDURE dbo.usp_Clear_Table
AS

/*
TRUNCATE TABLE _CreatedChars

TRUNCATE TABLE _DeletedChars

TRUNCATE TABLE CharApplySkills

TRUNCATE TABLE CharItems

TRUNCATE TABLE CharQuests

TRUNCATE TABLE CharQuickSlots

TRUNCATE TABLE Chars

TRUNCATE TABLE CharSkills

TRUNCATE TABLE GuildChars

TRUNCATE TABLE Guilds

TRUNCATE TABLE Keeps

TRUNCATE TABLE UserMaxGrow

TRUNCATE TABLE UserStoredItems

TRUNCATE TABLE UserStoredMoney
*/

RETURN 1
GO
07/09/2011 09:40 [DEV]xXDASHXx#7
here is my created quary just execute it to save the quary and then run the quary after by right clicking it and execute quary it dose a compleat item wipe resets all toons to lvl1 with starter stats and so on and spawnsall in there corect starteing location basicaly in short turns there account as if they freshly started 5 new toons and never played them yet
Code:
USE [PS_GMTool]
GO
/******
AUTHOR [DEV]xXDASHXx
Compleatly wipe all none GM accounts
 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[AccountSwipe]

AS
SET NOCOUNT ON

-- set level 1 Kill/Dead Level to zero;and k1/k2/k3/k4 to 0 and spawn all in AH 
UPDATE [PS_GameData].[dbo].[Chars] 
SET[Level]=1,[KillLevel]=0,[DeadLevel]=0,K1=0,K2=0,K3=0,K4=0,[EXP]=0,StatPoint=0,SkillPoint=5,
[Money]=0
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

--Update Players location
UPDATE PS_GameData.dbo.Chars SET [Map]=
   (CASE 
        WHEN Family=0 THEN 1
        WHEN Family=1 THEN 1 
        WHEN Family=2 THEN 2 
        WHEN Family=3 THEN 2
 ELSE [Map]
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)
 
UPDATE PS_GameData.dbo.Chars SET[PosX]=
    (CASE
		WHEN Family=0 AND Job=0 THEN  542  --Fighter
        WHEN Family=3 AND Job=0 THEN  1839  --war
        WHEN Family=0 AND Job=1 THEN  542 --def
        WHEN Family=3 AND Job=1 THEN  1839 --Gard
        WHEN Family=1 AND Job=2 THEN  1487 --Ranger
        WHEN Family=2 AND Job=2 THEN  165 --Sin
        WHEN Family=1 AND Job=3 THEN  1487 --Archer
        WHEN Family=3 AND Job=3 THEN  1839 --Hunter
        WHEN Family=1 AND Job=4 THEN  1487 --Mage
        WHEN Family=2 AND Job=4 THEN  165 --Pagan
        WHEN Family=0 AND Job=5 THEN  542 --Priest
        WHEN Family=2 AND Job=5 THEN  165 --Orc 
 ELSE PosX
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

UPDATE PS_GameData.dbo.Chars SET[PosY]=
    (CASE 
		WHEN Family=0 AND Job=0 THEN  79  --Fighter
        WHEN Family=3 AND Job=0 THEN  130  --war
        WHEN Family=0 AND Job=1 THEN  79 --def
        WHEN Family=3 AND Job=1 THEN  130 --Gard
        WHEN Family=1 AND Job=2 THEN  43 --Ranger
        WHEN Family=2 AND Job=2 THEN  119 --Sin
        WHEN Family=1 AND Job=3 THEN  43 --Archer
        WHEN Family=3 AND Job=3 THEN  130 --Hunter
        WHEN Family=1 AND Job=4 THEN  43 --Mage
        WHEN Family=2 AND Job=4 THEN  119 --Pagan
        WHEN Family=0 AND Job=5 THEN  79 --Priest
        WHEN Family=2 AND Job=5 THEN  119 --Orc
 ELSE PosY
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

UPDATE PS_GameData.dbo.Chars SET[PosZ]=
    (CASE
		WHEN Family=0 AND Job=0 THEN  1760  --Fighter
        WHEN Family=3 AND Job=0 THEN  444  --war
        WHEN Family=0 AND Job=1 THEN  1760 --def
        WHEN Family=3 AND Job=1 THEN  444 --Gard
        WHEN Family=1 AND Job=2 THEN  1575 --Ranger
        WHEN Family=2 AND Job=2 THEN  398 --Sin
        WHEN Family=1 AND Job=3 THEN  1575 --Archer
        WHEN Family=3 AND Job=3 THEN  444 --Hunter
        WHEN Family=1 AND Job=4 THEN  1575 --Mage
        WHEN Family=2 AND Job=4 THEN  398 --Pagan
        WHEN Family=0 AND Job=5 THEN  1760 --Priest
        WHEN Family=2 AND Job=5 THEN  398 --Orc 
 ELSE PosZ
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

--Update Players Base Stats
UPDATE PS_GameData.dbo.Chars SET [Str]=
   (CASE 
        WHEN Family=0 AND Job=0 THEN 14 --Fighter
        WHEN Family=3 AND Job=0 THEN 14 --war
        WHEN Family=0 AND Job=1 THEN 10 --def
        WHEN Family=3 AND Job=1 THEN 12 --Gard
        WHEN Family=1 AND Job=2 THEN 10 --Ranger
        WHEN Family=2 AND Job=2 THEN 10 --Sin
        WHEN Family=1 AND Job=3 THEN 11 --Archer
        WHEN Family=3 AND Job=3 THEN 13 --Hunter
        WHEN Family=1 AND Job=4 THEN 7  --Mage
        WHEN Family=2 AND Job=4 THEN 7  --Pagan
        WHEN Family=0 AND Job=5 THEN 8  --Priest
        WHEN Family=2 AND Job=5 THEN 8  --Orc 
 ELSE [Str]
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)
 
UPDATE PS_GameData.dbo.Chars SET[Dex]=
    (CASE 
        WHEN Family=0 AND Job=0 THEN 12 --Fighter
        WHEN Family=3 AND Job=0 THEN 12 --war
        WHEN Family=0 AND Job=1 THEN 9  --def
        WHEN Family=3 AND Job=1 THEN 9  --Gard
        WHEN Family=1 AND Job=2 THEN 19 --Ranger
        WHEN Family=2 AND Job=2 THEN 15 --Sin
        WHEN Family=1 AND Job=3 THEN 14 --Archer
        WHEN Family=3 AND Job=3 THEN 10 --Hunter
        WHEN Family=1 AND Job=4 THEN 13 --Mage
        WHEN Family=2 AND Job=4 THEN 9  --Pagan
        WHEN Family=0 AND Job=5 THEN 9  --Priest
        WHEN Family=2 AND Job=5 THEN 9  --Orc 
 ELSE Dex
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

UPDATE PS_GameData.dbo.Chars SET Rec=
    (CASE 
        WHEN Family=0 AND Job=0 THEN 9  --Fighter
        WHEN Family=3 AND Job=0 THEN 9  --war
        WHEN Family=0 AND Job=1 THEN 12 --def
        WHEN Family=3 AND Job=1 THEN 14 --Gard
        WHEN Family=1 AND Job=2 THEN 9  --Ranger
        WHEN Family=2 AND Job=2 THEN 9  --Sin
        WHEN Family=1 AND Job=3 THEN 10 --Archer
        WHEN Family=3 AND Job=3 THEN 12 --Hunter
        WHEN Family=1 AND Job=4 THEN 9  --Mage
        WHEN Family=2 AND Job=4 THEN 9  --Pagan
        WHEN Family=0 AND Job=5 THEN 10 --Priest
        WHEN Family=2 AND Job=5 THEN 10 --Orc 
 ELSE [Rec]
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

UPDATE PS_GameData.dbo.Chars SET [Int]=
   ( CASE 
        WHEN Family=0 AND Job=0 THEN 8  --Fighter
        WHEN Family=3 AND Job=0 THEN 8  --war
        WHEN Family=0 AND Job=1 THEN 10 --def
        WHEN Family=3 AND Job=1 THEN 10 --Gard
        WHEN Family=1 AND Job=2 THEN 7  --Ranger
        WHEN Family=2 AND Job=2 THEN 9  --Sin
        WHEN Family=1 AND Job=3 THEN 7  --Archer
        WHEN Family=3 AND Job=3 THEN 7  --Hunter
        WHEN Family=1 AND Job=4 THEN 15 --Mage
        WHEN Family=2 AND Job=4 THEN 17 --Pagan
        WHEN Family=0 AND Job=5 THEN 12 --Priest
        WHEN Family=2 AND Job=5 THEN 14 --Orc 
 ELSE [Int]
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

UPDATE PS_GameData.dbo.Chars SET [Wis]=
   ( CASE 
        WHEN Family=0 AND Job=0 THEN 7  --Fighter
        WHEN Family=3 AND Job=0 THEN 7  --war
        WHEN Family=0 AND Job=1 THEN 10 --def
        WHEN Family=3 AND Job=1 THEN 10 --Gard
        WHEN Family=1 AND Job=2 THEN 8  --Ranger
        WHEN Family=2 AND Job=2 THEN 10 --Sin
        WHEN Family=1 AND Job=3 THEN 10 --Archer
        WHEN Family=3 AND Job=3 THEN 10 --Hunter
        WHEN Family=1 AND Job=4 THEN 12 --Mage
        WHEN Family=2 AND Job=4 THEN 14 --Pagan
        WHEN Family=0 AND Job=5 THEN 14 --Priest
        WHEN Family=2 AND Job=5 THEN 16 --Orc 
 ELSE [Wis]
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

UPDATE PS_GameData.dbo.Chars SET [Luc]=
    (CASE 
        WHEN Family=0 AND Job=0 THEN 15 --Fighter
        WHEN Family=3 AND Job=0 THEN 15 --war
        WHEN Family=0 AND Job=1 THEN 14 --def
        WHEN Family=3 AND Job=1 THEN 10 --Gard
        WHEN Family=1 AND Job=2 THEN 12 --Ranger
        WHEN Family=2 AND Job=2 THEN 12 --Sin
        WHEN Family=1 AND Job=3 THEN 13 --Archer
        WHEN Family=3 AND Job=3 THEN 13 --Hunter
        WHEN Family=1 AND Job=4 THEN 9  --Mage
        WHEN Family=2 AND Job=4 THEN 9  --Pagan
        WHEN Family=0 AND Job=5 THEN 12 --Priest
        WHEN Family=2 AND Job=5 THEN 8  --Orc 
 ELSE [Luc]
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

UPDATE PS_GameData.dbo.Chars SET [HP]=
    (CASE 
        WHEN Family=0 AND Job=0 THEN  352  --Fighter
        WHEN Family=3 AND Job=0 THEN  352  --war
        WHEN Family=0 AND Job=1 THEN  6385 --def
        WHEN Family=3 AND Job=1 THEN  6385 --Gard
        WHEN Family=1 AND Job=2 THEN  6879 --Ranger
        WHEN Family=2 AND Job=2 THEN  6879 --Sin
        WHEN Family=1 AND Job=3 THEN  5583 --Archer
        WHEN Family=3 AND Job=3 THEN  5583 --Hunter
        WHEN Family=1 AND Job=4 THEN  5157 --Mage
        WHEN Family=2 AND Job=4 THEN  5157 --Pagan
        WHEN Family=0 AND Job=5 THEN  3261 --Priest
        WHEN Family=2 AND Job=5 THEN  3261 --Orc
 ELSE [HP]
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

UPDATE PS_GameData.dbo.Chars SET [MP]=
    (CASE 
        WHEN Family=0 AND Job=0 THEN  110  --Fighter
        WHEN Family=3 AND Job=0 THEN  110  --war
        WHEN Family=0 AND Job=1 THEN  495 --def
        WHEN Family=3 AND Job=1 THEN  495 --Gard
        WHEN Family=1 AND Job=2 THEN  518 --Ranger
        WHEN Family=2 AND Job=2 THEN  518 --Sin
        WHEN Family=1 AND Job=3 THEN  534 --Archer
        WHEN Family=3 AND Job=3 THEN  534 --Hunter
        WHEN Family=1 AND Job=4 THEN  593 --Mage
        WHEN Family=2 AND Job=4 THEN  593 --Pagan
        WHEN Family=0 AND Job=5 THEN  4501 --Priest
        WHEN Family=2 AND Job=5 THEN  4501 --Orc
 ELSE [MP]
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

UPDATE PS_GameData.dbo.Chars SET [SP]=
    (CASE 
        WHEN Family=0 AND Job=0 THEN  257  --Fighter
        WHEN Family=3 AND Job=0 THEN  257  --war
        WHEN Family=0 AND Job=1 THEN  557 --def
        WHEN Family=3 AND Job=1 THEN  557 --Gard
        WHEN Family=1 AND Job=2 THEN  555 --Ranger
        WHEN Family=2 AND Job=2 THEN  555 --Sin
        WHEN Family=1 AND Job=3 THEN  2081 --Archer
        WHEN Family=3 AND Job=3 THEN  2081 --Hunter
        WHEN Family=1 AND Job=4 THEN  2099 --Mage
        WHEN Family=2 AND Job=4 THEN  2099 --Pagan
        WHEN Family=0 AND Job=5 THEN  452 --Priest
        WHEN Family=2 AND Job=5 THEN  452 --Orc
 ELSE [SP]
END)
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID AND UM.[Status] NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT UserUID INTO #CharTemp1 FROM  PS_UserData.dbo.Users_Master Where [Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp2 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=0 AND C.Job=0 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp3 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=0 AND C.Job=1 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp4 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=0 AND C.Job=5 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp5 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=1 AND C.Job=3 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp6 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=1 AND C.Job=2 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp7 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=1 AND C.Job=4 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp8 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=3 AND C.Job=0 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp9 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=3 AND C.Job=1 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp10 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=2 AND C.Job=5 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp11 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=3 AND C.Job=3 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp12 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=2 AND C.Job=2 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp13 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON C.Family=2 AND C.Job=4 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp14 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON  C.Job = 1 and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

SELECT CharID INTO #CharTemp15 FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
    ON  C.Job IN (0,2,3,4,5,6) and C.UserUID = UM.UserUID AND UM.[Status]  NOT IN (16,32,48,64,80)

--Deleate Skills

DELETE FROM [PS_GameData].[dbo].[CharSkills] WHERE [CharID] IN (SELECT [CharID] FROM #CharTemp);

    -- delete all Quick Slots
DELETE FROM [PS_GameData].[dbo].[CharQuickSlots] WHERE [CharID] IN (SELECT [CharID] FROM #CharTemp);

--Update Equiped Items
update [PS_GameData].[dbo].[CharItems]Set ItemID=1001,[Type]=1,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp2) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=7001,[Type]=7,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp3) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=12001,[Type]=12,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp4) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=13001,[Type]=13,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp5) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=9001,[Type]=9,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp6) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=12001,[Type]=12,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp7) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=3001,[Type]=3,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp8) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=7006,[Type]=7,TypeID=6, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp9) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=12006,[Type]=12,TypeID=6, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp10) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=13006,[Type]=13,TypeID=6, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp11) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=9001,[Type]=9,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp12) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=12006,[Type]=12,TypeID=6, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp13) and Bag=0 and Slot=5;

update [PS_GameData].[dbo].[CharItems]Set ItemID=19001,[Type]=19,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp3) and Bag=0 and Slot=6;

update [PS_GameData].[dbo].[CharItems]Set ItemID=34001,[Type]=19,TypeID=1, Gem1=0, Gem2=0, Gem3=0, Gem4=0, Gem5=0, Gem6=0, CraftName=00000000000000000000
 WHERE [CharID] IN 
(SELECT [CharID] FROM #CharTemp9) and Bag=0 and Slot=6;

--deleate  all none equiped weps/shields
DELETE FROM [PS_GameData].[dbo].[CharItems] WHERE [CharID] IN (SELECT [CharID] FROM #CharTemp14) and Bag IN ( 1 ,2 ,3 ,4 ,5 ,6 ,7);

DELETE FROM [PS_GameData].[dbo].[CharItems] WHERE [CharID] IN (SELECT [CharID] FROM #CharTemp14) and 
Slot IN(0,1,2,3,4,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);

DELETE FROM [PS_GameData].[dbo].[CharItems] WHERE [CharID] IN (SELECT [CharID] FROM #CharTemp15) and Bag IN ( 1,2,3,4,5,6,7,8,9);

DELETE FROM [PS_GameData].[dbo].[CharItems] WHERE [CharID] IN (SELECT [CharID] FROM #CharTemp15) and 
Slot IN (0,1,2,3,4,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);

--deleat all WH Items
DELETE FROM [PS_GameData].[dbo].[UserStoredItems] WHERE [UserUID] IN (SELECT [UserUID] FROM #CharTemp1);


--Deletion of Teleportation Spots
DELETE FROM [PS_GameData].[dbo].[CharSavePoint] WHERE [CharID] IN (SELECT [CharID] FROM #CharTemp);
--makeshure ItemID is exact
Update PS_GameData.dbo.CharItems set ItemID = [Type]*1000+TypeID where CharID=CharID

--Dropping temporary tables
DROP TABLE #CharTemp
Drop Table #CharTemp1
Drop Table #CharTemp2
Drop Table #CharTemp3
Drop Table #CharTemp4
Drop Table #CharTemp5
Drop Table #CharTemp6
Drop Table #CharTemp7
Drop Table #CharTemp8
Drop Table #CharTemp9
Drop Table #CharTemp10
Drop Table #CharTemp11
Drop Table #CharTemp12
Drop Table #CharTemp13
Drop Table #CharTemp14
Drop Table #CharTemp15 

SET NOCOUNT OFF