TRUNCATE scat_item;

INSERT INTO scat_item
       (code, retail_price, discount_type, discount, stock)
SELECT code, retail_price, discount_type, discount,
       IFNULL((SELECT SUM(allocated)
                 FROM scat.txn_line
                WHERE item = scat.item.id), 0) stock
  FROM scat.item;

TRUNCATE scat_item;

INSERT INTO scat_item
       (code, retail_price, discount_type, discount, stock)
SELECT code, retail_price, discount_type, discount,
       IF(active,
          (SELECT SUM(allocated) FROM scat.txn_line WHERE item = scat.item.id),
          NULL) stock
  FROM scat.item;

Set stock quantity to NULL for inactive products
Set stock quantity to NULL for inactive products
