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
(more doc)
m (Woozle moved page VbzCart/VbzCart/procs/Upd Depts fr DeptIttyps to VbzCart/docs/procs/Upd Depts fr DeptIttyps without leaving a redirect: part 2)
 
(3 intermediate revisions by one other user not shown)
Line 1: Line 1:
==About==
==About==
* '''Purpose''': Updates some fields in {{vbzcart|table|_depts}} after that has been filled in by {{vbzcart|proc|Upd_Depts_fr_Depts_Suppliers}}
* '''Purpose''': Updates some fields in {{vbzcart|table|_depts}} after that has been filled in by {{vbzcart|proc|Upd_Depts_fr_Depts_Suppliers}}
* '''Input''': {{vbzcart|table|_dept_ittyps}} (group by ID_Dept)
* '''Input''': {{vbzcart/query|qryCat_Titles_Item_stats}} (group by ID_Dept)
* '''Output''': {{vbzcart|table|_depts}} (update)
* '''Output''': {{vbzcart|table|_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''' &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>CREATE PROCEDURE Upd_Depts_fr_DeptIttyps()
<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Depts_fr_DeptIttyps;
CREATE PROCEDURE Upd_Depts_fr_DeptIttyps()
     UPDATE _depts AS d LEFT JOIN (
     UPDATE _depts AS d LEFT JOIN (
       SELECT
       SELECT
Line 10: 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

Latest revision as of 01:55, 25 February 2024

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 />

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/procs/Upd_Depts_fr_DeptIttyps&oldid=6711"