VbzCart/docs/procs/Upd Titles fr CatItems: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (New page: ==About== * '''Purpose''': sets some fields in {{vbzcart|table|_titles}} after that has been filled in by {{vbzcart|proc|Upd_Titles_fr_Depts}} ==SQL== <section begin=sql /><mysql>CREATE PR...) |
m (Woozle moved page VbzCart/VbzCart/procs/Upd Titles fr CatItems to VbzCart/docs/procs/Upd Titles fr CatItems without leaving a redirect: part 2) |
||
| (2 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* ''' | * '''Action''': sets some fields in {{vbzcart|table|_titles}} after that has been filled in by {{vbzcart|proc|Upd_Titles_fr_Depts}} | ||
* '''Input''': {{vbzcart|table|cat_items}} | |||
* '''Output''': {{vbzcart|table|_titles}}: cntForSale, cntInPrint, qtyInStock | |||
* '''History''': | |||
** '''2010-11-10''' | |||
*** Added DROP PROCEDURE for easier maintenance. | |||
*** Added to database (procs did not get ported from L48 to Rizzo). | |||
*** i.qtyInStock -> i.qtyIn_Stk | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE PROCEDURE Upd_Titles_fr_CatItems() | <section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Titles_fr_CatItems; | ||
CREATE PROCEDURE Upd_Titles_fr_CatItems() | |||
UPDATE _titles AS t LEFT JOIN ( | UPDATE _titles AS t LEFT JOIN ( | ||
SELECT | SELECT | ||
| Line 8: | Line 16: | ||
SUM(IF(i.isForSale,1,0)) AS cntForSale, | SUM(IF(i.isForSale,1,0)) AS cntForSale, | ||
SUM(IF(i.isInPrint,1,0)) AS cntInPrint, | SUM(IF(i.isInPrint,1,0)) AS cntInPrint, | ||
SUM(i. | SUM(i.qtyIn_Stk) AS qtyInStock, | ||
MIN(i.PriceSell) AS currMinSell, | MIN(i.PriceSell) AS currMinSell, | ||
MAX(i.PriceSell) AS currMaxSell | MAX(i.PriceSell) AS currMaxSell | ||
Latest revision as of 01:55, 25 February 2024
About
- Action: sets some fields in Template:Vbzcart after that has been filled in by Template:Vbzcart
- Input: Template:Vbzcart
- Output: Template:Vbzcart: cntForSale, cntInPrint, qtyInStock
- History:
- 2010-11-10
- Added DROP PROCEDURE for easier maintenance.
- Added to database (procs did not get ported from L48 to Rizzo).
- i.qtyInStock -> i.qtyIn_Stk
- 2010-11-10
SQL
<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Titles_fr_CatItems; CREATE PROCEDURE Upd_Titles_fr_CatItems()
UPDATE _titles AS t LEFT JOIN (
SELECT
ID_Title,
SUM(IF(i.isForSale,1,0)) AS cntForSale,
SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
SUM(i.qtyIn_Stk) AS qtyInStock,
MIN(i.PriceSell) AS currMinSell,
MAX(i.PriceSell) AS currMaxSell
FROM cat_items AS i GROUP BY ID_Title
) AS ig ON ig.ID_Title=t.ID
SET
t.cntForSale = ig.cntForSale,
t.cntInPrint = ig.cntInPrint,
t.qtyInStock = ig.qtyInStock;</mysql>
<section end=sql />