asked
code incomplete please help
here is the data Wat i wat is a little strange most of you will undestand from the code > SET XACT_ABORT, NOCOUNT ON; > > BEGIN TRY > > BEGIN TRAN > > DECLARE @Members TABLE (memb_id > VARCHAR(10)) > > > UPDATE MEMBINFO SET cspoints = > a.cspoints + b.PCPoints OUTPUT > INSERTED.memb_id > INTO @Members FROM [CHARACTER] b > INNER JOIN [MEMBINFO] a > ON b.AccountID = a.memb_id > > UPDATE [CHARACTER] SET PCPoints = > 0 WHERE AccountID IN ( SELECT > memb_id > FROM @Members AS M ) > > > IF @@TRANCOUNT > 0 COMMIT TRAN END > TRY BEGIN CATCH > > SELECT ERROR_MESSAGE(), ERROR_LINE() > IF @@TRANCOUNT > 0 AND XACT_STATE <> 0 > ROLLBACK TRAN > > END CATCH The code was made by usmabut .. and has a glich ,(Bug) , woks fine when the player on my server has on his AccountID only one Name (character ) but the problem is that the player can create 5 of these characters [Name] on one [AccountID] so 5 difrente Name with the same ACCOUNT id ... Problem in the code is that we he opens a new characters and has 1 PCPoints bug the code no conversion to any of the players Wat i need is to ADD first all PCPpoints from the ALL Name in the Character table with the same AccountID then the result add with cspoints from MEMB_INFO is with the same AccountID (From Character) = memb___id (From MEMB_INFO) > TotalPCpoints from Character + cspoints = TotalCSpoints after that Set PCPoints 0 can any one help please for more details i am here here tables > USE [MuOnline] > > GO > > > /****** Object: Table > [dbo].[Character] Script Date: > 06/12/2012 01:32:46 **/ > > SET ANSI_NULLS ON > > > GO > > > SET QUOTED_IDENTIFIER ON > > > GO > > > SET ANSI_PADDING ON > > > GO > > > CREATE TABLE [dbo].[Character]( > > > [AccountID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, > > > [Name] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, > > > [cLevel] [int] NULL CONSTRAINT [DF_Character_cLevel] DEFAULT ((1)), > > > [LevelUpPoint] [int] NULL CONSTRAINT [DF_Character_LevelUpPoint] > DEFAULT ((0)), > > [Class] [tinyint] NULL, > > > [Experience] [int] NULL CONSTRAINT [DF_Character_Experience] DEFAULT > ((0)), > > [Strength] [smallint] NULL, > > > [Dexterity] [smallint] NULL, > > > [Vitality] [smallint] NULL, > > > [Energy] [smallint] NULL, > > > [Inventory] [varbinary](1728) NULL, > > > [MagicList] [varbinary](180) NULL, > > > [Money] [int] NULL CONSTRAINT [DF_Character_Money] DEFAULT ((0)), > > > [Life] [real] NULL, > > > [MaxLife] [real] NULL, > > > [Mana] [real] NULL, > > > [MaxMana] [real] NULL, > > > [MapNumber] [smallint] NULL, > > > [MapPosX] [smallint] NULL, > > > [MapPosY] [smallint] NULL, > > > [MapDir] [tinyint] NULL CONSTRAINT [DF_Character_MapDir] DEFAULT ((0)), > > [PkCount] [int] NULL CONSTRAINT [DF_Character_PkCount] DEFAULT ((0)), > > [PkLevel] [int] NULL CONSTRAINT [DF_Character_PkLevel] DEFAULT ((3)), > > [PkTime] [int] NULL CONSTRAINT [DF_Character_PkTime] DEFAULT ((0)), > > > [MDate] [smalldatetime] NULL, > > > [LDate] [smalldatetime] NULL, > > > [CtlCode] [tinyint] NULL CONSTRAINT [DF_Character_CtlCode] > DEFAULT ((0)), > > [DbVersion] [tinyint] NULL CONSTRAINT > [DFCharacterDbVer__3A4CA8FD] > > DEFAULT ((0)), > > [Quest] [varbinary](50) NULL CONSTRAINT > [DFCharacterQuest__40F9A68C] > > DEFAULT ((0)), > > [Leadership] [smallint] NULL CONSTRAINT > [DFCharacterLeade__6FB49575] > > DEFAULT ((0)), > > [ChatLimitTime] [smallint] NULL CONSTRAINT > [DFCharacterChatL__70A8B9AE] > > DEFAULT ((0)), > > [FruitPoint] [int] NULL CONSTRAINT [DFCharacterFruit__44CA3770] > > DEFAULT ((0)), > > [JHDX] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL, > > > [JHtype] [tinyint] NULL, > > > [cspoints] [int] NULL, > > > [Grand_Resets] [int] NOT NULL CONSTRAINT > [DFCharacterGrand__42E1EEFE] > > DEFAULT ((0)), > > [mu_id] [int] IDENTITY(1,1) NOT NULL, > > > [Resets] [int] NOT NULL CONSTRAINT [DFCharacterReset__43D61337] > > DEFAULT ((0)), > > [EDSPostCmd] [int] NOT NULL CONSTRAINT [DF_Character_EDSPostCmd] > DEFAULT ((0)), > > [CirePkRank] [int] NOT NULL CONSTRAINT [DF_Character_CirePkRank] > DEFAULT ((0)), > > [EDSPostBanTime] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL > CONSTRAINT > > [DFCharacterEDSPostBanTime] DEFAULT > > ((0)), > > [Married] [tinyint] NOT NULL CONSTRAINT [DF_Character_Married] > DEFAULT ((0)), > > [MarryName] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL, > > > [GensRank] [smallint] NOT NULL CONSTRAINT [DF_Character_GensRank] > DEFAULT ((0)), > > [GensContribution] [int] NOT NULL CONSTRAINT > [DFCharacterGensContribution] > > DEFAULT ((0)), > > [GensType] [tinyint] NOT NULL CONSTRAINT [DF_Character_GensType] > DEFAULT ((0)), > > [GensLastLeave] [int] NOT NULL CONSTRAINT [DFCharacterGensLastLeave] > DEFAULT > > ((0)), > > [GensKnight] [tinyint] NOT NULL DEFAULT ((0)), > > > [PCPoints] [int] NOT NULL DEFAULT ((0)), > > > [TempPcPoints] [int] NOT NULL DEFAULT ((0)), > > > CONSTRAINT [PK_Character] PRIMARY KEY > NONCLUSTERED > > > ( > > > [Name] ASC > > > )WITH (IGNORE_DUP_KEY = OFF) ON > [PRIMARY] > > > ) ON [PRIMARY] > > > > > > GO > > > SET ANSI_PADDING OFF USE [MuOnline] GO > /** Object: > > Table [dbo].[MEMB_INFO] Script > > Date: 06/12/2012 01:33:04 **/ SET > > ANSINULLS ON GO SET QUOTEDIDENTIFIER > > ON GO SET ANSI_PADDING ON GO CREATE > > TABLE [dbo].[MEMB_INFO]( > > [memb_guid] [int] IDENTITY(1,1) NOT > NULL, > > > [memb___id] [varchar](10) COLLATE > Chinese_PRC_CI_AS NOT NULL, > > > [memb__pwd] [varchar](10) COLLATE > Chinese_PRC_CI_AS NOT NULL, > > > [memb_name] [varchar](10) COLLATE > Chinese_PRC_CI_AS NOT NULL, > > > [sno__numb] [char](13) COLLATE > Chinese_PRC_CS_AS_KS_WS NOT NULL, > > > [post_code] [char](6) COLLATE > Chinese_PRC_CS_AS_KS_WS NULL, > > > [addr_info] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [addr_deta] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [tel__numb] [varchar](20) COLLATE > Chinese_PRC_CI_AS NULL, > > > [phon_numb] [varchar](18) COLLATE > Chinese_PRC_CI_AS NULL, > > > [mail_addr] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [fpas_ques] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [fpas_answ] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [job__code] [char](2) COLLATE > Chinese_PRC_CI_AS NULL, > > > [appl_days] [datetime] NULL, > > > [modi_days] [datetime] NULL, > > > [out__days] [datetime] NULL, > > > [true_days] [datetime] NULL, > > > [mail_chek] [char](1) COLLATE > Chinese_PRC_CI_AS NULL CONSTRAINT > [DFMEMBINFOmailchek] DEFAULT > > ((0)), > > [bloc_code] [char](1) COLLATE > Chinese_PRC_CI_AS NOT NULL, > > > [ctl1_code] [char](1) COLLATE > Chinese_PRC_CI_AS NOT NULL, > > > [JF] [int] NULL CONSTRAINT > [DF__MEMB_INFO__JF__10566F31] DEFAULT > ((0)), > > [servercode] [int] NOT NULL CONSTRAINT > [DF_MEMB_INFO_servercode] DEFAULT > ((0)), > > [dingdan] [varbinary](50) NULL, > > > [usedtime] [int] NOT NULL CONSTRAINT > [DF_MEMB_INFO_usedtime] DEFAULT > ((0)), > > [cspoints] [int] NULL CONSTRAINT > [DF_MEMB_INFO_cspoints] DEFAULT > ((0)), > > [activation_id] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [confirmed] [int] NULL, > > > [Country] [nvarchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [Gender] [int] NULL, > > > [SecretAnswer] [varchar](100) COLLATE > Chinese_PRC_CI_AS NULL, > > > [SecretQuestion] [int] NULL, > > > [VipStat] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [VipStamp] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [MasterKey] [varchar](10) COLLATE > Chinese_PRC_CI_AS NULL, > > > [AccountLock] [tinyint] NULL > CONSTRAINT [DFMEMBINFOAccou_2AD55B43] > > DEFAULT ((0)), > > [Remark] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [TempCredits] [int] NULL CONSTRAINT > [DFMEMBINFOTempC_2BC97F7C] > > DEFAULT ((0)), > > [memb__pwd2] [varchar](50) COLLATE > Chinese_PRC_CI_AS NULL, > > > [votes] [int] NOT NULL CONSTRAINT > [DF__MEMB_INFO__votes__6E8B6712] > DEFAULT ((0)), CONSTRAINT > > [PKMEMBINFO] PRIMARY KEY > > NONCLUSTERED ( > > [memb___id] DESC )WITH (IGNORE_DUP_KEY > = OFF) ON [PRIMARY] ) ON [PRIMARY] > > GO SET ANSI_PADDING OFF
Thank you KenJ please thank beandon10 for support .. the code corected by KenJ ------------------------------------------------------------- UPDATE MEMB_INFO SET cspoints = a.cspoints + b.PCPoints OUTPUT INSERTED.memb_id INTO @Members FROM [MEMB_INFO] a INNER JOIN (SELECT sum(PCPoints) as PCPoints, AccountID FROM [CHARACTER] GROUP BY AccountID ) AS b ON b.AccountID = a.memb_id ---------------------------------------------------- **So the corect script is this one** ---------------------------------------------------------------------------------------- SET XACT_ABORT, NOCOUNT ON; BEGIN TRY BEGIN TRAN DECLARE @Members TABLE (memb___id VARCHAR(10)) UPDATE MEMB_INFO SET cspoints = a.cspoints + b.PCPoints OUTPUT INSERTED.memb___id INTO @Members FROM [MEMB_INFO] a INNER JOIN (SELECT sum(PCPoints) as PCPoints, AccountID FROM [CHARACTER] GROUP BY AccountID ) AS b ON b.AccountID = a.memb___id UPDATE [CHARACTER] SET PCPoints = 0 WHERE AccountID IN ( SELECT memb___id FROM @Members AS M ) IF @@TRANCOUNT > 0 COMMIT TRAN END TRY BEGIN CATCH SELECT ERROR_MESSAGE(), ERROR_LINE() IF @@TRANCOUNT > 0 AND XACT_STATE () <> 0 ROLLBACK TRAN END CATCH \
From what I can see of your code, the issue is the fact that there will be a different value for a.cspoints + b.PCPoints in the update for each of the characters the player has. In order to get that to work correctly, you will either want to sum up the character points (b.PCPoints) into a temporary table grouped by the AccountID, or use a sub query within the update statement to aggregate the character points.
Here is the subquery approach to summing the points that Beandon10 mentioned. Do the `JOIN` against the subquery that does the sum instead of joining directly to the character table INNER JOIN (SELECT sum(PCPoints) as PCPoints, AccountID FROM [CHARACTER] GROUP BY AccountID ) AS b ON b.AccountID = a.memb_id
That is corect i have to add ALL PCPpoints of all Character with the same account id the problem is that in cspoint i have the same problem ... so i need to add the result there to but to all his account LEt me say a secenario Character Table you have your account With a character / then you go play make some PCPoints .. there is your pcpoint colum with 10 pcpoints in it but before the conversion is made to Cspoints you create a second character with PCPoint colum set to 0 and play now in MEMB_INFO (The cspoint table) you have 2 acounts with 0 CSpoints Wen the script hits .. you will have the 10 PCPoints converted in 10 Cspoints but later Back o Character Table .. You are in the game and kill some creeps that give you PCPoints on the same acoount id first you login with your first character and you make 15 pcpoints swich to second character and make antoner 25 pcpoints the problem with this is that you dont have 40 pcpoints you have 15 with 25 ... so wen the script hits you will have a bug
........................................ So the MEMB_INFO table is working with a single Character do not matter how many character you have ... is taking the FRIST character you have build and puts all cspoints there .. all othere Characer with the SAME accountID will take the cspoints form the first character info .. in fact dont mater the charer is just the basic information of the account you can see in the colums So wat i need first is to add the PCPoints of all characters with the same accountdi then the result Add with PCPoints in my noob head will sound like this
> UPDATE [CHARACTER] SET TempPCPoints = Add all of PCPoints with the same AccountID > UPDATE MEMB_INFO SET cspoints = a.cspoints + b.TempPCpoints OUTPUT > INSERTED.memb_id INTO @Members FROM > [CHARACTER] b INNER JOIN [MEMB_INFO] a > ON b.AccountID = a.memb_id And then i hope the bug will be fixed
I would recommend pulling the sum of the PCPoints from the Character table into a temp table before that. Then, join that to the MEMB_INFO table to update the cspoints. If you try it as above, you still have to create a subquery from teh Character table to get the sum of PCPoints per AccountID. So if you start by creating the temp table, and then insert SUM(PCPoints), AccountID FROM Character you should be able to use that temp table to update the MEMB_INTO table.
12 People are following this question.
