asked
SQL colum -1 to user
So imagine 2 colums in 1 table letz sat Table name "Gold_Cest" and the colums 1 "Members" colum 2 "Gold" now the thing i want to take -1 Gold from the tolat gold of members in the Gold_Cest table i am not so advance of the sql i am stil reading the commands so if any one can help here will buy me some time and i will be greatefull P.S i dont wat -1 to all members just the -1 form the Total (sum) Q> do i need to build another Table then move constant the Sum of Members Gold ? then -1 if yes how do i do that
UPDATE Gold_Cest SET Gold = Gold - 1 WHERE Members = 'some member' > P.S i dont wat -1 to all members just > the -1 form the Total (sum) which member ?
This is the point A list to members letz say Table= Gold Cest members Gold Cristian 50 Mirqu 30 Drag 10 The sum will be 90 Gold And each minute -1 will disapear
-1 Wil ldisapear from the sum of 90 but if any of them adds more will add to his member id and of course to the sum Example Cristian adds 50 Gold then cristian will have 100 but because in the meen time letz say 60 min the gold has disapear -1 all members suffers gold los 90 - 60 And then each of them has diffrence equal to 3 members / 60 wich is 20 so now Table = Gold Cest Members Gold Cristian 30 Mirqu 10 Drag -10 Because Drag has - ( he will pay 10 and end up with 0) and the process repeats 10 Gold left /2 Cristian pays 5 and Mirqu pays 5 so this is why i need a new table or colum in a table to make hte Gold Chest or somting and then -1 from there each minute
Is more complicate like i have explain it so i think more easy is to transfer the SUM of all members in a diffrent colum wich is alone and has no members for example Table name Castle_Cest Members > Gold_Cest Castle 90 And then write UPDATE Gold_Cest fom Castle_Cest SET Gold_Cest = Gold - 1 WHERE Members = 'Castle ' Then instalntly UPDATE Gold_Cest fom Gold_Cest SET Gold_Cest = 0 But before of this i shood activate SQL JOB doing this Like Select * of Gold from Gold_Cest the result Update Gold_Cest + (Sum of Gold) From Gold_Cest
But this will declare a problem if in the second this is made the freaky any of members will update the database So this SQL fraze must be done in one shot
something like this. declare @gold int select @gold = 60 update gc set gold = gc.gold - @gold / (select count(*) from Gold_Cest) from Gold_Cest gc You will still need to handle cases where you can't divide the gold to deduct equally among members
i need to modify this i think 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 / 2) 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 COLLATE Chinese_PRC_CI_AS 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
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 / 2) 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 COLLATE Chinese_PRC_CI_AS 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
15 People are following this question.
