每分钟更新一次活动的清单

debugcn 发表于 Dev

用户名

我想自动更新我的listings表的最高buyer_ID列bidder_ID在bids表中。我的问题是...如何从表中获取与bid_listing关联listing_ID的内容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

开发银行

基于代码示例和您对“出价表中最高的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

希望对您有所帮助

问候

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。