CODEWITHSUNDEEP
mysql
Kris
Kris

Reputation: 59

Inserting data from another table

I have two tables:

Stock Data

Batch Stock Rate 
------------------------
123---ABC---123 
251---XYZ---50
250---XYZ---55
247---XYZ---48
118---ABC---120
310---PQR---77
309---PQR---76

and the second table Order Book

Date-------Stock---qty--Rate
--------------------------
2016-01-01---ABC---10---Null
2016-01-02---XYZ---25---Null
2016-01-03---PQR---18---Null
2016-01-04---XYZ---50---Null

I am stuck with inserting the Rate in the Order book table from Stock Data where the rate the in the stock data should be of the latest batch for each product. Thanks in advance

Upvotes: 3

Views: 57

Answers (3)

Rakesh Kumar
Rakesh Kumar

Reputation: 4420

Try This Query:-

UPDATE order_book ob, (
          SELECT rate, stock 
          FROM stock_data sd 
          GROUP BY stock 
          HAVING MAX(batch) 
          ORDER BY batch DESC
    ) result
SET ob.rate = result.rate
WHERE ob.stock = result.stock

Upvotes: 0

1000111
1000111

Reputation: 13519

Please try the following query:

UPDATE OrderBook OB
INNER JOIN (
    SELECT
        SD.Stock,
        SD.Rate
    FROM StockData SD
    INNER JOIN (
        SELECT
            Stock,
            MAX(Batch) max_batch
        FROM StockData
        GROUP BY Stock
    ) AS t 
  ON SD.Stock = t.Stock AND SD.Batch = t.max_batch
) AS maxBatchRateTable 
ON OB.Stock = maxBatchRateTable.Stock
SET OB.Rate = maxBatchRateTable.Rate;

Explanation:

This query

    SELECT
        SD.Stock,
        SD.Rate
    FROM    StockData SD
    INNER JOIN (
        SELECT
            Stock,
            MAX(Batch) max_batch
        FROM StockData
        GROUP BY Stock
    ) AS t 
  ON SD.Stock = t.Stock AND SD.Batch = t.max_batch

finds the rate from the latest batch for each stock.

Later if you make an INNER JOIN between this table and your OrderBook table on matching Stock and update the Rate in OrderBook table by the rate value returned by the above query you will get what you want.

Upvotes: 1

Philipp
Philipp

Reputation: 2796

UPDATE
   order_book   
INNER JOIN
   (
      SELECT
         Rate,
         Stock   
      FROM
         stock_data  
      INNER JOIN
         (
            SELECT
               MAX(Batch) AS batch,
               stock    
            FROM
               stock_data    
            GROUP BY
               Stock  
         ) AS latestBatch    
            ON stock_data.Batch = latestBatch.batch 
            AND stock_data.stock = latestBatch.stock  
         ) AS updateData 
            ON updateData.Stock = order_book.Stock  
      SET
         order_book.Rate = updateData.Rate

Should be along those lines

Upvotes: 0

Related Questions