Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Here is the query that doesn't compile just to show how it should look like. But since I can't do Multiple table UPDATE's.

This fake query should be able to run the inner UPDATE's for both tables if only a row exists from the inner SELECT. Then all those UPDATE's should be ran by 1 select at a time that's why the inner select has a LIMIT 1.. because if it selects more then one row at a time the next row match could be wrong or duplicate Seller (who is suppose to be unavailable) for a new Buyer from previous rows.

Should run those UPDATE's with the outer LIMIT of 100 each time (not guaranteed there will be 100 but it's a fair limit)

Also like to add that the amount a Buyer can buy from a Seller is randomized by the max amount that Buyer can buy subtracted from max amount seller can sell which is subtracted from amount that seller already sold..

I can't update the Seller's sold column or Buyer's bought column until I do some other queries with the program I'm building.

The query doesn't have I could add this myself later, to sell to Buyer's who pay the most money first.

SELECT * FROM Buying AS BB, Selling AS SS 
WHERE (
   UPDATE Buying, Selling
   SET Buying.boughtPending = Buying.boughtPending + willBuyAmount,
   SET Selling.soldPending = Selling.soldPending + willBuyAmount
      WHERE EXISTS (
           SELECT
                  CASE
                       WHEN( B.amount - B.bought ) >( S.amount - S.sold ) THEN( 1 + ABS( RANDOM(  ) %( S.amount - S.sold )  )  ) 
                       ELSE( 1 + ABS( RANDOM(  ) %( B.amount - B.bought )  )  ) 
                  END AS willBuyAmount
             FROM Buying AS B, 
                  Selling AS S
           ON B.itemId = S.itemId 
           AND
           B.aborted = 0 
           AND
           S.aborted = 0 
           AND
           B.boughtPending = 0 
           AND
           S.soldPending = 0 
           AND
           B.price >= S.price 
           AND
           S.sold < S.amount 
           AND
           B.bought < B.amount
           LIMIT 1 
      )
  ) AND BB.boughtPending > 0 AND SS.soldPending > 0 LIMIT 100;

I started trying out this new way to just get one match UPDATED (then I'd have to run this 100 times somehow. (doubt i'll be using VIEW's for this because I'll have to keep destroying them everytime I need to UPDATE (still doesn't work for just one match)

Error:

Error while executing query: no such column: sale.willBuyAmount

BEGIN;

CREATE TEMP VIEW sale AS
       SELECT CASE
                   WHEN( B.amount - B.bought ) >( S.amount - S.sold ) THEN( 1 + ABS( RANDOM(  ) %( S.amount - S.sold )  )  ) 
                   ELSE( 1 + ABS( RANDOM(  ) %( B.amount - B.bought )  )  ) 
              END AS willBuyAmount,
              B.id AS buyerId,
              S.id AS sellerId
         FROM Buying AS B, 
              Selling AS S
       ON B.itemId = S.itemId 
       AND
       B.aborted = 0 
       AND
       S.aborted = 0 
       AND
       B.boughtPending = 0 
       AND
       S.soldPending = 0 
       AND
       B.price >= S.price 
       AND
       S.sold < S.amount 
       AND
       B.bought < B.amount
       LIMIT 1;

UPDATE Buying   
SET boughtPending = boughtPending + sale.willBuyAmount  
WHERE sale.buyerId = id;

UPDATE Selling    
SET soldPending = soldPending + sale.willBuyAmount  
WHERE sale.sellerId = id;

DROP VIEW sales;

COMMIT;

here is my table DDL's

CREATE TABLE Buying ( 
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    itemId        INTEGER NOT NULL,
    amount        INTEGER NOT NULL,
    price         INTEGER NOT NULL,
    bought        INTEGER NOT NULL,
    boughtPending INTEGER NOT NULL
                          DEFAULT ( 0 ),
    collectedItem INTEGER NOT NULL
                          DEFAULT ( 0 ),
    collectedGold INTEGER NOT NULL
                          DEFAULT ( 0 ),
    overpaid      INTEGER NOT NULL
                          DEFAULT ( 0 ),
    slot          INTEGER NOT NULL,
    aborted       BOOLEAN NOT NULL
                          DEFAULT ( 0 ),
    playerHash    INTEGER NOT NULL,
    UNIQUE ( slot, playerHash ) 
);

CREATE TABLE Selling ( 
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    itemId        INTEGER NOT NULL,
    amount        INTEGER NOT NULL,
    price         INTEGER NOT NULL,
    sold          INTEGER NOT NULL,
    soldPending   INTEGER NOT NULL
                          DEFAULT ( 0 ),
    collectedItem INTEGER NOT NULL
                          DEFAULT ( 0 ),
    collectedGold INTEGER NOT NULL
                          DEFAULT ( 0 ),
    overpaid      INTEGER NOT NULL
                          DEFAULT ( 0 ),
    slot          INTEGER NOT NULL,
    aborted       BOOLEAN NOT NULL
                          DEFAULT ( 0 ),
    playerHash    INTEGER NOT NULL,
    UNIQUE ( slot, playerHash ) 
);
share|improve this question
    
Have you tried prototyping the query in smaller sections instead of creating one big blob. Additionally have you tried creating a temporary table instead of a temporary view in your second example. –  Ian Lewis Dec 23 '13 at 16:58
    
I haven't created a temporary table because I still haven't figured out how to use those.. VIEW's seem to do the same as I would use the temporary table for to view variables in a different query (UPDATE). Only thing is in a temporary table I would have to do a CREATE.. then INSERT.. then DROP TABLE at the end.. in VIEW I just run one command for CREATE+INSERT (I still don't know if this is why you use VIEW for). I also don't know how to INSERT from a SELECT.. so yeah I jsut need some help some examples if you have. –  SSpoke Dec 23 '13 at 20:14
    
You can't store data in a view, it is a way of storing a query to get a 'VIEW' of the data you are interested in. A temporary table is just that, a table, thus you can run all the table operations against it. I recommend making heavy use of the MySql online documentation and trying out a few things yourself. See this SO question for how to do an INSERT..SELECT stackoverflow.com/questions/5391344/insert-with-select –  Ian Lewis Dec 24 '13 at 9:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.