Difference between revisions of "VbzCart/procs/Upd Depts fr DeptIttyps"
Jump to navigation
Jump to search
(New page: ==About== * '''Purpose''': Updates some fields in {{vbzcart|table|_depts}} after that has been filled in by {{vbzcart|proc|Upd_Depts_fr_Depts_Suppliers}} ==SQL== <section begin=sql /><mysq...) |
(more doc) |
||
| 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) | ||
| + | * '''Output''': {{vbzcart|table|_depts}} (update) | ||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE PROCEDURE Upd_Depts_fr_DeptIttyps() | <section begin=sql /><mysql>CREATE PROCEDURE Upd_Depts_fr_DeptIttyps() | ||
Revision as of 21:21, 8 March 2009
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)
SQL
<mysql>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>