VbzCart/procs/Upd CatItems fr StkItems StkBins: Difference between revisions
from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
New page: ==About== * '''Action''': updates availability from current stock ==SQL== <section begin=sql /><mysql>CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins() UPDATE cat_items AS i LEFT JOI... |
m input/output doc |
||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Action''': updates availability from current stock | * '''Action''': updates availability from current stock | ||
* '''Input''': {{vbzcart|table|stk_items}}, {{vbzcart|table|stk_bins}} | |||
* '''Output''': {{vbzcart|table|cat_items}} | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins() | <section begin=sql /><mysql>CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins() | ||
Revision as of 18:26, 8 March 2009
About
SQL
<section begin=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>
<section end=sql />
