mysql - 每分钟更新一次列表的事件

Question

我正在尝试使用 bids 表中最高的 bidder_ID 自动更新 listings 表的 Buyer_ID 列。我的问题是...如何从 listing 表中获取与 listing_ID 相关的 bid_listing？

我当前的事件查询:

CREATE EVENT updateAuction
ON SCHEDULE EVERY 1 MINUTE
DO
    SET buyer_ID = (SELECT MAX(bidder_ID) FROM bids WHERE bid_listing = (Need each listing_ID from the listings table here)) 
    FROM listings
    WHERE list_expires < now()
    AND buyer_ID = NULL

出价架构

bid_ID bid_listing bidder_ID bid_amount

列表架构

listing_ID list_expires buyer_ID sold_price

Answer 1

基于代码示例和您的“出价表中最高的 bidder_ID”声明

我认为您需要的是UPDATE JOIN

Update listings set
   buyer_id = table2.max_bidder
From listings table1
Inner Join ( Select bids.listing_ID
                  , max(bids.bidder_ID) as max_bidder 
             From bids
             Group by listing_ID ) table2
        on ( table1.listing_ID = table2.listing_ID )
WHERE list_expires < now()
    AND buyer_ID is NULL

这是使用 SUB QUERIES 来获取具有最高出价金额的 bidder_id 的UPDATE JOIN的另一个示例。

UPDATE listings set
    buyer_id   = table2.bidder_ID 
  , sold_price = table2.bid_amount  /* Optional */
From listings table1
Inner Join ( Select bids.listing_ID, bids.bidder_ID, bids.bid_amount
             From bids
             Inner Join ( Select bids.listing_ID 
                               , max(bid_amount) as bid_amount
                          From bids
                          Inner Join listings 
                                  on ( bids.listing_ID = bids.listing_ID )
                          Where list_expires < now()
                            and buyer_ID is NULL 
                        ) winning_amount
                    on (     bids.listing_ID = winning_amount.listing_ID 
                         and bids.bid_amount = winning_amount.bid_amount
                       )
           ) table2
        on ( table1.listing_ID = table2.listing_ID )
WHERE list_expires < now()
    AND buyer_ID is NULL

希望对你有帮助

问候