Please help me with my SQL code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please help me with my SQL code

Author

Topic

zohanel
Starting Member

7 Posts

Posted - 2012-06-01 : 22:28:24

but for this problem

Helo i have a probleme here from the table Character Coloana 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 Coloana cspoints but after i conver them i want to be like this
before conversion
PCPoints = 100 cspoints = 0
and after i want to have
Pcpoints = 0 cspoints =100
here are my tables Tabelele

Wat i want
Character& MEMB_INFO

i 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 iner JOIN MEMB_STAT ON Character.AccountID=MEMB_INFO.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
[/quote]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 23:49:09

your update statement doest make much sense as i cant see what a,b etc refer to. Also how are tables Character and MEMB_STAT related?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zohanel
Starting Member

7 Posts

Posted - 2012-06-06 : 08:19:11

memb_INFO not stat

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-06 : 10:36:11

That update statement is very confusing. There are what appear to be table aliases that are not defined, etc.. Can you post some DDL, DML and Expected Results?

Here are two links that can help you prepare that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

zohanel
Starting Member

7 Posts

Posted - 2012-06-10 : 15:37:53

I dont know how o make a DDL or DML ,

the end result wat i expect is

IF i have 100 PCPoints and 5 cspoints

after job / exchange

i want to have 0 PCpoints and 105 Cspoints

I have travel the forums in a serch of this answer and found this


SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRAN

DECLARE @Members TABLE ( memb_id VARCHAR(10) )

UPDATE  MEMB_INFO
SET     cspoints = a.cspoints + b.PCPoints
OUTPUT  DELETED.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 )

    .............................
Do the COMMIT, CATCH EXCEPTION, ROLLBACK etc

i dont know how to use it in my situation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-10 : 15:50:18

ok your code looks fine

you just need to add END TRY
BEGIN CATCH
..error block
END CATCH

is that what you want our help on?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zohanel
Starting Member

7 Posts

Posted - 2012-06-11 : 18:35:34

[code]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
[/code]

Note that the CSpoints from Character is usless i use them for somting else , the server do not take info from there thatz why i need to make this swich

[code]SE [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[/code]

so wat i want is this

From table A Character to move all data from colum PCPoints
in to the MEMB_INFO table B and + add it , to colum cspoints

is like this 100 PCPoints (a) and 10 cspoints(b)
querry run the script
then 0 PCPoints (a) and 110 cspoints (b)

[code]
ET XACT_ABORT ON;

BEGIN TRY

BEGIN TRAN

DECLARE @Members TABLE ( memb_id VARCHAR(10) )

UPDATE MEMB_INFO
SET cspoints = a.cspoints + b.PCPoints
OUTPUT DELETED.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 )

.............................
Do the COMMIT, CATCH EXCEPTION, ROLLBACK etc
[/code]

#visakh16

quote:
ok your code looks fine you just need to add

[code]END TRY
BEGIN CATCH
..error block
END CATCH [/code]

-----------------------------------------------------------------------------
I dont know how to add or modify

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:19:22

[code]
SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRAN

DECLARE @Members TABLE ( memb_id VARCHAR(10) )

UPDATE MEMB_INFO
SET cspoints = a.cspoints + b.PCPoints
OUTPUT DELETED.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 )
COMMIT TRAN
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRAN

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zohanel
Starting Member

7 Posts

Posted - 2012-06-14 : 22:33:39

Just try this and work thanks to usman butt Thank you visaky i will try now your code to see if it works your help will not be fogatten i will come back with the results

[url]http://ask.sqlservercentral.com/questions/89728/sql-comands-please-help-me.html#answer-89809[/url]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources