May 12, 2017 at 5:23 am
Hi,
I'm just learning Microsoft SQL so please can anyone help me write the following..
I am developing a small betting site and I have tblAccounts and tblBackers.
I wish to update a users balance column in tblAccounts when they have bet on the winner.
Bets are stored in tblBackers which has columns amountBacked, backersAccountID and the user/player they have chose to win (playerBacked)
The winning accountID is passed in as @accountID so I need it to check tblBackers and update the balance of backersPlayerID with the amountBacked for every occurrance of playerBacked = @accountID
I was way off trying this..
CREATE PROCEDURE [dbo].[UpdatePayout]
@accountID uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT playerBacked from tblBackers)=@accountID
BEGIN
UPDATE tblAccounts
SET balance = balance + (SELECT amountBacked FROM tblBackers WHERE playerBacked = @accountID)
WHERE AccountID=(SELECT backersAccountID from tblBackers)
END
END
I got the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <= , >, >= or when the subquery is used as an expression."
Many thanks for any help.
May 12, 2017 at 7:04 am
my first reaction is thinking outside the corner you are painting yourself in.
a balance like that should be calculated on demand, as a query or as a view, so you can simply sum() group by accountID
if an error is introduced(ie you fail to add ten dollars to my account when it was supposed to), how do you know what the balance REALLY is?
Your "tblAccouunts" should realy be a view if it the summary of accounts and amounts.
do you insert winnings or losses into a transaction table of some kind?
Lowell
May 12, 2017 at 7:21 am
I didn't have your real table structure, but this is a best guess on if you HAVE to have a static tableCREATE PROCEDURE [dbo].[UpdatePayout]
@accountID uniqueidentifier
AS
BEGIN
--we need to recalculate the balance from the details
WITH MyCTESummary
AS
(SELECT
SUM(amountBacked) AS TotalBacked,
backersAccountID
FROM tblBackers
GROUP BY backersAccountID
)
--update the static table from the details
UPDATE MyTarget
SET balance = TotalBacked
FROM tblAccounts MyTarget
INNER JOIN MyCTESummary MySource
ON MyTarget.AccountID = MySource.backersAccountID
--only for the account in question
WHERE MyTarget..AccountID = @accountID
END
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply