Querying the result set

Databases
21

And I’m gonna tick Rudy off more and suggest a long arsed query; one long arsed in execution, and one long arsed in length.


SELECT productid,quantity,available FROM table;



while($row = mysql_fetch_array($res)) {
  if(($row['available'] = 0 && ($row['quantity'] >= 1 || $list[$row['productid']] >= 1)) || ($row['available'] = 1 && $row['quantity'] == 0 && $list[$row['productid']] == 0)) {
    $out[] = $row['productid'];
  }
}
$string = implode(',',$out);



UPDATE table SET available = available + 1 MOD 2 WHERE productid IN ( $string )
22

Create a temporary landing table for the vendor data than a query like the below can be used.

(proof of concept)


UPDATE
     products
 INNER
  JOIN
     tmp_stock
    ON
     products.partnumber = tmp_stock.partnumber
   SET
    products.availability = tmp_stock.availability