Difference between revisions of "VbzCart/procs/Upd Titles fr TitleIttyps"
Jump to navigation
Jump to search
(New page: ==About== * '''Purpose''': Updates some fields in {{vbzcart|table|_titles}} * '''Input''': {{vbzcart|table|_title_ittyps}}, grouped by ID_Title * '''Output''': {{vbzcart|table|_titles}} (u...) |
(created in db) |
||
| Line 3: | Line 3: | ||
* '''Input''': {{vbzcart|table|_title_ittyps}}, grouped by ID_Title | * '''Input''': {{vbzcart|table|_title_ittyps}}, grouped by ID_Title | ||
* '''Output''': {{vbzcart|table|_titles}} (update) | * '''Output''': {{vbzcart|table|_titles}} (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_Titles_fr_TitleIttyps() | + | <section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Titles_fr_TitleIttyps; |
| + | CREATE PROCEDURE Upd_Titles_fr_TitleIttyps() | ||
UPDATE _titles AS t LEFT JOIN ( | UPDATE _titles AS t LEFT JOIN ( | ||
SELECT | SELECT | ||
Revision as of 15:19, 10 November 2010
About
- Purpose: Updates some fields in _titles
- Input: _title_ittyps, grouped by ID_Title
- Output: _titles (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_Titles_fr_TitleIttyps; CREATE PROCEDURE Upd_Titles_fr_TitleIttyps()
UPDATE _titles AS t LEFT JOIN (
SELECT
ID_Title,
SUM(ti.cntForSale) AS cntForSale,
SUM(ti.cntInPrint) AS cntInPrint,
SUM(ti.qtyInStock) AS qtyInStock
FROM _title_ittyps AS ti GROUP BY ID_Title
) AS ti ON ti.ID_Title=t.ID
SET
t.cntForSale = ti.cntForSale,
t.cntInPrint = ti.cntInPrint,
t.qtyInStock = ti.qtyInStock;</mysql>