Search code examples
phpmysqlsqlperformancequery-optimization

MYSQL : SET value in an UPDATE query when all conditions of subquery are satisfied

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);
}
Solution

  • 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.