Difference between revisions of "VbzCart/procs/Upd Depts fr DeptIttyps"
Jump to navigation
Jump to search
(more doc) |
(created in db) |
||
| Line 3: | Line 3: | ||
* '''Input''': {{vbzcart|table|_dept_ittyps}} (group by ID_Dept) | * '''Input''': {{vbzcart|table|_dept_ittyps}} (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). | ||
==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 | ||
Revision as of 15:10, 10 November 2010
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).
- 2010-11-10
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>