dongzhimin2231
dongzhimin2231
2016-06-10 19:24

MYSQL:满足子查询的所有条件时UPDATE查询中的SET值

已采纳

Problem

There are 2 tables DEALS and DEAL_DETAILS.

DEALS table contains title and other generic information about the deal.

DEAL_DETAILS table contains information on different time ranges at which deal has to be shown, address specific to deal etc.,

I have abstracted the columns we need to focus on and have attached the schema below.

What I need to achieve is-

When ALL status-es of a particular dealid in DEAL_DETAILS is 'completed' I would like to update the status in DEALS table as 'completed'.

Giving an example-

The status of 1013 in DEALS table should be 'completed'. As ALL statuses of 1013 linked(Foreign Key) dealid in DEAL_DETAILS are in 'completed' status.

Whereas, that's not the case with 1012 as 2 are in 'completed' status and other 2 have different status. So, status in DEALS table should NOT be changed.

And, 1011 is out of question!

My schema looks like this-

DEALS:

enter image description here

DEAL_DETAILS:

enter image description here

I did try some SQL queries by referring to this, this, this and this. My query is incomplete and doesn't look good. Currently, I have achieved the same inefficiently(I guess) using PHP(please ignore if there are change in column names).

while ($row = mysqli_fetch_assoc($aggregateStatusresult)) {
    //$return_array[$i++] = $row;
    if(strcmp($row->status, 'completed') && (!(isset($statusarray[$row->dealid])) || ($statusarray[$row->dealid] != 'completed')))
        $statusarray[$row->dealid] = 'completed';
    else
        $statusarray[$row->dealid] = $row->status;
}
foreach($statusarray  as $dealid => $status){
    $updateAggregateStatus = "update deals d set d.status='".$status."' where d.dealid = '".$dealid."'";
    $updateAggregateStatusresult = $connection->query($updateAggregateStatus);
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dpjuppr1361 dpjuppr1361 5年前

    You should be able to handle it with a simple NOT EXISTS check:

    UPDATE deals d SET d.status='approved'
      WHERE NOT EXISTS (SELECT * FROM deal_details
        WHERE deal_details.dealid = d.id AND status <> 'completed');
    

    This assumes there are always rows in the deal_details table, if there are none it will mark the deal approved.

    点赞 评论 复制链接分享

相关推荐