SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Stock System: Prevent Negative Quantities or Large Numbers

    Guys,

    We have a stock system that uses a very simple concept of adjusting stock. It basically says:
    Code MySQL:
    UPDATE `stocktable` SET `qty` = `qty` - 1 WHERE `id` = 123456
    which works fine in most cases, except when we're trying to remove more stock than we should. As this is an unsigned BIGINT, if we remove two items from stock but the system says that there are only one actually in stock, we end up with 18446744073709551615, simulated by:
    Code MySQL:
    SELECT CAST(1 AS UNSIGNED) - 2
    Is there a way to avoid this? The only way we've seen so far is something like
    Code MySQL:
    UPDATE `stocktable` SET `qty` = `qty` - 2 WHERE `id` = 123456 AND `qty` >= 2
    .

    Are there any better solutions?

    Cheers

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You could check if the stock is sufficient before you do the update.
    Otherwise, I think the solution you found is the only one.

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yeah, the problem is that there's a chance that another query could have adjusted the stock level between the two queries, is there not? Unlikely, but possible. I think we'll stick with the previous solution then. Cheers

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what two queries? there's only one, the UPDATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    1) Check
    2) Update

    Putting them both together is surely the original query?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •