VbzCart/procs/Upd Depts fr DeptIttyps

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 15:10, 10 November 2010 by Woozle (talk | contribs) (created in db)
Jump to navigation Jump to search

About

  • Purpose: Updates some fields in _depts after that has been filled in by Upd_Depts_fr_Depts_Suppliers
  • Input: _dept_ittyps (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).

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.qtyInStock) AS qtyInStock
     FROM _dept_ittyps 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>