在一个查询中进行选择和更新 [英] Making select and update in one query

问题描述

是否有一个查询，我可以一次完成两个查询?

is there a query where I can do both querys in one?

这是第一个

$q = "select c.id as campaignId,c.priceFactor,
              o.cid,o.bloggerPrice,o.state as state,o.customerPrice,o.id as orderId,o.listPrice,o.basicPrice
              from campaign c, orders o
              where c.id={$campaignId}
              and c.id = o.cid
              and o.state in (8,9)";

这是第二个

  foreach($orders as $order)
        {
             $listPrice      = $order->priceFactor * $order->basicPrice;

             if($order->bloggerPrice < $listPrice || $order->customerPrice < $listPrice)
             {
                $order->bloggerPrice  = $listPrice;
                $order->customerPrice = $listPrice;
             }

             $qUpdate       = "update orders set
                               listPrice = {$listPrice},bloggerPrice={$order->bloggerPrice},
                               customerPrice ={$order->customerPrice}
                               where id=$order->orderId and cid={$order->cid}";

            // $this->db->q($qUpdate);
        }

我的问题是:仅使用纯SQL，是否可以在没有PHP代码的情况下完成以上操作?

My question is: Can I do it the above without a PHP code just pure SQL?

推荐答案

在MySQL中，您可以在UPDATE之后立即使用联接.在您的示例中，这可能类似于:

In MySQL, you can use a join right after UPDATE. In your example, this might look something like:

update Orders o
inner join Campaign c on c.id = o.cid
set
    listPrice = o.priceFactor * order.basicPrice
,   bloggerPrice = case 
        when o.bloggerPrice < o.priceFactor * order.basicPrice
            then o.priceFactor * order.basicPrice
            else bloggerPrice
        end
,   listPrice = case 
        when o.customerPrice < o.priceFactor * order.basicPrice
            then o.priceFactor * order.basicPrice
            else listPrice
        end
where o.state in (8,9)
and c.id = {$campaignId}

这篇关于在一个查询中进行选择和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！