Difference between revisions of "VbzCart/procs/Upd Titles fr TitleIttyps"
Jump to navigation
Jump to search
(created in db) |
m (removed obsolete tag) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Purpose''': Updates some fields in {{vbzcart|table|_titles}} | * '''Purpose''': Updates some fields in {{vbzcart|table|_titles}} | ||
| − | * '''Input''': {{vbzcart| | + | * '''Input''': {{vbzcart/query|qryCat_Titles_Item_stats}}, grouped by ID_Title |
* '''Output''': {{vbzcart|table|_titles}} (update) | * '''Output''': {{vbzcart|table|_titles}} (update) | ||
* '''History''': | * '''History''': | ||
| 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 recursive loop: this→{{vbzcart|table|_titles}}→{{vbzcart|proc|Upd_TitleIttyps_fr_Titles}}→{{vbzcart|table|_title_ittyps}}→this, so changed source from {{vbzcart|table|_title_ittyps}} to {{vbzcart/query|qryCat_Titles_Item_stats}}. | ||
==SQL== | ==SQL== | ||
| − | + | <mysql>DROP PROCEDURE IF EXISTS Upd_Titles_fr_TitleIttyps; | |
CREATE PROCEDURE Upd_Titles_fr_TitleIttyps() | CREATE PROCEDURE Upd_Titles_fr_TitleIttyps() | ||
UPDATE _titles AS t LEFT JOIN ( | UPDATE _titles AS t LEFT JOIN ( | ||
| Line 15: | Line 16: | ||
SUM(ti.cntForSale) AS cntForSale, | SUM(ti.cntForSale) AS cntForSale, | ||
SUM(ti.cntInPrint) AS cntInPrint, | SUM(ti.cntInPrint) AS cntInPrint, | ||
| − | SUM(ti. | + | SUM(ti.qtyForSale) AS qtyInStock |
| − | FROM | + | FROM qryCat_Titles_Item_stats AS ti GROUP BY ID_Title |
) AS ti ON ti.ID_Title=t.ID | ) AS ti ON ti.ID_Title=t.ID | ||
SET | SET | ||
| Line 22: | Line 23: | ||
t.cntInPrint = ti.cntInPrint, | t.cntInPrint = ti.cntInPrint, | ||
t.qtyInStock = ti.qtyInStock;</mysql> | t.qtyInStock = ti.qtyInStock;</mysql> | ||
| − | |||
Latest revision as of 01:23, 7 September 2015
About
- Purpose: Updates some fields in _titles
- Input:
- REDIRECT Template:l/vc/query, 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).
- 2011-12-24 this was in a recursive loop: this→_titles→Upd_TitleIttyps_fr_Titles→_title_ittyps→this, so changed source from _title_ittyps to qryCat_Titles_Item_stats.
- 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.qtyForSale) AS qtyInStock
FROM qryCat_Titles_Item_stats 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>