VbzCart/procs/Upd CatItems fr StkItems StkBins

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | procs
Revision as of 18:26, 8 March 2009 by Woozle (talk | contribs) (input/output doc)
Jump to navigation Jump to search

About

SQL

<mysql>CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins()

   UPDATE cat_items AS i LEFT JOIN (
       SELECT
           si.ID_Item,
           SUM(si.Qty) AS QtyInStock
        FROM stk_items AS si LEFT JOIN stk_bins AS sb ON si.ID_Bin=sb.ID
        WHERE (sb.isForSale) AND ISNULL(sb.WhenVoided) AND ISNULL(si.WhenRemoved)
        GROUP BY ID_Item
       ) AS sig ON i.ID=sig.ID_Item
     SET
        i.QtyInStock = sig.QtyInStock,
        i.isForSale = i.isInPrint OR (sig.QtyInStock > 0);</mysql>