SQL Comands please help me

Question

Helo i have a probleme here from the table Character >PCPointsi have players that paly o this game and win PCPoints letz say 100 and i want to make a php or a SQL JOB , this PCPoints in Tabel MEMB_INFO > cspoints but after replace them and te same time to add them Example before conversion **

PCPoints = 100 and cspoints = 5

** and after i want to have **

Pcpoints = 0 and cspoints =105

** the formula will be a+b = c and a = 0 where A= PCpoints and b=cspoints and c=a+b

alt text Character MEMB_INFO

have try to make this code via the book but is not working i mess up i have made a progres but the PCPoints did not delete after conversion

Update MEMB_INFO Set cspoints = a.cspoints+b.PCPoints FROM Character.b iner JOIN MEMBINFO.a ON Character.AccountID=MEMBINFO.memb_id where a=cspoints and b=PCPoints

is verry immportant after the conversion the player have 0 PCPoints , because i dont want them to update the cspoints forever please help

Answer 1

First of all, please help us to help you better by posting the DDLs of the tables, some test data and the desired output.

Now, If I understand correctly, what you want is to add character points to the member and then re-initialize the character points after the addition. One way of doing it is to get the updated Members into a Declared table by using OUTPUT clause and then using the same info to re-initialize the character points with zero but in a Transaction mode. Something like

USE [tempdb]
GO
IF OBJECT_ID('tempdb.dbo.CHARACTER') IS NOT NULL
DROP TABLE [CHARACTER]

IF OBJECT_ID('tempdb.dbo.MEMBINFO') IS NOT NULL
DROP TABLE dbo.MEMBINFO

CREATE TABLE dbo.MEMBINFO
(
    memb_id VARCHAR(10),
    cspoints INT
)

CREATE TABLE dbo.CHARACTER
(
    AccountID VARCHAR(10),
    PCPoints INT
)

GO
/*==== INITIALIZE THE MEMBINFO WITH SOME DUMMY DATA ===*/
INSERT [MEMBINFO]
        (
         [memb_id]
        ,[cspoints]
        )
SELECT '1', 10
UNION ALL
SELECT '2',20
UNION ALL
SELECT '3',30
UNION ALL
SELECT '4',40

/*=== INSERT DUMMY DATA INTO #CHARACTER TABLE =====*/
INSERT [CHARACTER]
        (
         [AccountID]
        ,[PCPoints]
        )
SELECT '1', 10
UNION ALL
SELECT '2',20
UNION ALL
SELECT '3',30
-- EXCLUDED THE MEMBER 4
GO

SET XACT_ABORT 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 )


SELECT * FROM [dbo].[CHARACTER] AS C

SELECT * FROM [dbo].[MEMBINFO] AS M

COMMIT TRAN
 END TRY
BEGIN CATCH

SELECT ERROR_MESSAGE(), ERROR_LINE()

ROLLBACK TRAN

END CATCH

Answer 2

The sript is perfect it works first time but later in the procces give some erros and do not Upload the results in the cspoints from MEMB_INFO

My ideea was that the users from Table Character do not have only one Character so the bug apears wen the same account has multply character with pcpoints and do not add up

So first i shood ADD all the PCPoints of the SAME account , so wat i need is to add first the PCPoints from the Character table with the same AccountID then The result add with cspoints from MEMB_INFO ....

I think this solution will fix my problem .. can anny one help me with the code here .. and merry cirstmas to all here

Answer 3

The sript is perfect it works first time but later in the procces give some erros and do not Upload the results in the cspoints from MEMB_INFO

My ideea was that the users from Table Character do not have only one Character so the bug apears wen the same account has multply character with pcpoints and do not add up

So first i shood ADD all the PCPoints of the SAME account , so wat i need is to add first the PCPoints from the Character table with the same AccountID then The result add with cspoints from MEMB_INFO ....

I think this solution will fix my problem .. can anny one help me with the code here .. and merry cirstmas to all here

Answer 4

0

i dot know for wat reason the main code is not working

dose not show any error , but is not compelte the transaction to add the cspoints and it deletes the PcPoints at the end fist time wen i used it work fine but after i iserted in the SQL > JOB , to execute the code each wekend , the code fail to do this for 2 weeks in a row , and wen i try to use it again just write Query compelte no errors but he did not achive the main subject .... usman ? are you there ?

code is here https://www.dropbox.com/s/p8oipr87j5ukflp/errorlogs.txt

And copy here but will coppy without some lines _

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 [Character] b
INNER JOIN [MEMB_INFO] 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

answered Jul 03 '12 at 08:37 PM

zohan
20 ● 1 ● 4 ● 5

@zohan Sorry for the delay but I was too busy at work. If it is not giving any error then there is no syntax error. But there could be some logical errors like the JOIN columns do not have the same data or no data at all. That I guess can only be identified by yourself.

Jul 05 '12 at 06:06 AM Usman Butt

i have tested on the acounts , and wen i put 1000 PCPoints to my acount i run the script the PCPoints go to 0 , but on the cspoints is 0 to

I have tested with data on cspoints and realise that on colum cspoints dont arive anything because i have put 500 cspoints and after i run again the simulation the pcpoints was 0 and cspoints 500 no change only on the pcpoints

So wat happens is that the pcpoints get to 0 and the cspoints dont get any data from the pcpoints colums ..

I have try to copy the right colums but still no progres wat cood be a problem

Still i did not click execute and click the for use maby this video will help Video link

Jul 07 '12 at 09:07 AM zohan

Anyone know wat is the pproblem here ? i have try like crazy did not work why?

Jul 12 '12 at 11:19 PM zohan

@zohan Can you please post some data from both the tables? Then it would be easier for me to understand the problem.

Jul 13 '12 at 05:41 AM Usman Butt

i have post the tables dll theere wat data do you need , ?

Thid you see the video Link

Jul 15 '12 at 06:16 PM zohan

show 4 more replies

(comments are locked)

Answer 5

So this is all the data i have it here , from the 2 tables , wat data do you need from them

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 [DF__Character__DbVer__3A4CA8FD]  DEFAULT ((0)),
        [Quest] [varbinary](50) NULL CONSTRAINT [DF__Character__Quest__40F9A68C]  DEFAULT ((0)),
        [Leadership] [smallint] NULL CONSTRAINT [DF__Character__Leade__6FB49575]  DEFAULT ((0)),
        [ChatLimitTime] [smallint] NULL CONSTRAINT [DF__Character__ChatL__70A8B9AE]  DEFAULT ((0)),
        [FruitPoint] [int] NULL CONSTRAINT [DF__Character__Fruit__44CA3770]  DEFAULT ((0)),
        [JHDX] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
        [JHtype] [tinyint] NULL,
        [cspoints] [int] NULL,
        [Grand_Resets] [int] NOT NULL CONSTRAINT [DF__Character__Grand__42E1EEFE]  DEFAULT ((0)),
        [mu_id] [int] IDENTITY(1,1) NOT NULL,
        [Resets] [int] NOT NULL CONSTRAINT [DF__Character__Reset__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 [DF_Character_EDSPostBanTime]  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 [DF_Character_GensContribution]  DEFAULT ((0)),
        [GensType] [tinyint] NOT NULL CONSTRAINT [DF_Character_GensType]  DEFAULT ((0)),
        [GensLastLeave] [int] NOT NULL CONSTRAINT [DF_Character_GensLastLeave]  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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER 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 [DF_MEMB_INFO_mail_chek]  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 [DF__MEMB_INFO__Accou__2AD55B43]  DEFAULT ((0)),
    [Remark] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [TempCredits] [int] NULL CONSTRAINT [DF__MEMB_INFO__TempC__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 [PK_MEMB_INFO] PRIMARY KEY NONCLUSTERED 
(
    [memb___id] DESC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

SQL Comands please help me

Follow this question

Related Questions