1 Replies - 348 Views - Last Post: 12 May 2017 - 05:06 AM

#1 eggybread  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 04-May 17

Updating a users balance for every bet won

Posted 12 May 2017 - 03:46 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)[email protected]
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.

Is This A Good Question/Topic? 0
  • +

Replies To: Updating a users balance for every bet won

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: Updating a users balance for every bet won

Posted 12 May 2017 - 05:06 AM

First, this is questionable:
IF (SELECT playerBacked from tblBackers)[email protected]



Why the parens? Actually, what does that even mean?

To get the if to work:
IF EXISTS (SELECT * from tblBackers where [email protected])



The other one, though:
WHERE AccountID=(SELECT backersAccountID from tblBackers)


There's no saving that.

However, storing values in a field is simply not a good idea. You'd have to call this very single time tblBackers changed.

As a rule of thumb, you should not store values you can simply derive from data you already have.

So, can you do something like:
select a.*, b.balance
  from tblAccounts a
    left outer join (
      -- your subquery here
      ) b on a.AccountID=b.AccountID



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1