VbzCart/procs/Upd Titles fr TitleIttyps: Difference between revisions

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Woozle (talk | contribs)
Bureaucrats, Interface administrators, interwiki, Administrators, trusted
20,104 edits
VisualWikitext
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...
 
m removed obsolete tag
 
(3 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|table|_title_ittyps}}, grouped by ID_Title
* '''Input''': {{vbzcart/query|qryCat_Titles_Item_stats}}, 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).
** '''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==
<section begin=sql /><mysql>CREATE PROCEDURE Upd_Titles_fr_TitleIttyps()
<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
Line 10: Line 16:
       SUM(ti.cntForSale) AS cntForSale,
       SUM(ti.cntForSale) AS cntForSale,
       SUM(ti.cntInPrint) AS cntInPrint,
       SUM(ti.cntInPrint) AS cntInPrint,
       SUM(ti.qtyInStock) AS qtyInStock
       SUM(ti.qtyForSale) AS qtyInStock
     FROM _title_ittyps AS ti GROUP BY ID_Title
     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 17: Line 23:
       t.cntInPrint = ti.cntInPrint,
       t.cntInPrint = ti.cntInPrint,
       t.qtyInStock = ti.qtyInStock;</mysql>
       t.qtyInStock = ti.qtyInStock;</mysql>
<section end=sql />

Latest revision as of 01:23, 7 September 2015

About

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>
Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/procs/Upd_Titles_fr_TitleIttyps&oldid=21235"