VbzCart/procs/Upd Depts fr DeptIttyps

About

• Purpose: Updates some fields in _depts after that has been filled in by Upd_Depts_fr_Depts_Suppliers
• Input:

1. REDIRECT Template:l/vc/query (group by ID_Dept)

• Output: _depts (update)
• History:
  • 2010-11-10
    • Added DROP PROCEDURE for easier maintenance.
    • Created in database (procs were not ported from L48 to Rizzo).
  • 2011-12-24 this was in a long recursive loop: this → _depts → Upd_Titles_fr_Depts → _titles → Upd_TitleIttyps_fr_Titles → _title_ittyps → Upd_DeptIttyps_fr_TitleIttyps → _dept_ittyps → this, so replacing source _dept_ittyps with qryCat_Titles_Item_stats

SQL

<mysql>DROP PROCEDURE IF EXISTS Upd_Depts_fr_DeptIttyps; CREATE PROCEDURE Upd_Depts_fr_DeptIttyps()

   UPDATE _depts AS d LEFT JOIN (
     SELECT
       ID_Dept,
       SUM(di.cntForSale) AS cntForSale,
       SUM(di.cntInPrint) AS cntInPrint,
       SUM(di.qtyForSale) AS qtyInStock
     FROM qryCat_Titles_Item_stats AS di GROUP BY ID_Dept
     ) AS di ON di.ID_Dept=d.ID
     SET
       d.cntForSale = di.cntForSale,
       d.cntInPrint = di.cntInPrint,
       d.qtyInStock = di.qtyInStock;</mysql>