vendredi 4 décembre 2015

Updating SQLite Boolean field based on Sum of Another Field

I'm having a bit of trouble finessing the SQLite syntax here, and would love it if someone could point me on the right path.

Basically: I'm trying to update a boolean field in one table (MasterItems.InStock) based on the sum of a field in another table (Items.Qty) depending on whether the sum of Qty in the Items table is > 0 or not.

I can do it in two statements by first:

UPDATE MasterItems 
   SET InStock = 0 WHERE InStock <> 0;

UPDATE MasterItems 
   SET InStock = 1 
   WHERE ProductNumber IN
      (SELECT ProductNumber FROM Items
        GROUP BY Items
        HAVING SUM(Qty)> 0);

But I know there has to be a way to do it in a single statement...

Aucun commentaire:

Enregistrer un commentaire