VbzCart/docs/procs/Upd Depts fr DeptIttyps: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | procs
Jump to navigation Jump to search
imported>Woozle
(created in db)
imported>Woozle
(fixing infinite loop)
Line 7: Line 7:
*** Added DROP PROCEDURE for easier maintenance.
*** Added DROP PROCEDURE for easier maintenance.
*** Created in database (procs were not ported from L48 to Rizzo).
*** Created in database (procs were not ported from L48 to Rizzo).
** '''2011-12-24''' this was in a long recursive loop: '''this''' &rarr; {{vbzcart|table|_depts}} &rarr; {{vbzcart|proc|Upd_Titles_fr_Depts}} &rarr; {{vbzcart|table|_titles}} &rarr; {{vbzcart|proc|Upd_TitleIttyps_fr_Titles}} &rarr; {{vbzcart|table|_title_ittyps}} &rarr; {{vbzcart|proc|Upd_DeptIttyps_fr_TitleIttyps}} &rarr; {{vbzcart|table|_dept_ittyps}} &rarr; '''this''', so replacing source {{vbzcart|table|_dept_ittyps}} with {{vbzcart/query|qryCat_Titles_Item_stats}}
==SQL==
==SQL==
<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Depts_fr_DeptIttyps;
<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Depts_fr_DeptIttyps;
Line 15: Line 16:
         SUM(di.cntForSale) AS cntForSale,
         SUM(di.cntForSale) AS cntForSale,
         SUM(di.cntInPrint) AS cntInPrint,
         SUM(di.cntInPrint) AS cntInPrint,
         SUM(di.qtyInStock) AS qtyInStock
         SUM(di.qtyForSale) AS qtyInStock
       FROM _dept_ittyps AS di GROUP BY ID_Dept
       FROM qryCat_Titles_Item_stats AS di GROUP BY ID_Dept
       ) AS di ON di.ID_Dept=d.ID
       ) AS di ON di.ID_Dept=d.ID
       SET
       SET

Revision as of 22:03, 24 December 2011

About

SQL

<section begin=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>

<section end=sql />