Difference between revisions of "VbzCart/procs/Upd Depts fr Depts Suppliers"
Jump to navigation
Jump to search
(New page: ==About== * '''Purpose''': fills {{vbzcart|table|_depts}} (does not set inPrint/inStock fields) ==SQL== <section begin=sql /><mysql> CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers() BE...) |
(updated source table names; more doc) |
||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Purpose''': fills {{vbzcart|table|_depts}} (does not set inPrint/inStock fields) | * '''Purpose''': fills {{vbzcart|table|_depts}} (does not set inPrint/inStock fields) | ||
| + | * '''Input''': {{vbzcart|table|cat_depts}}, {{vbzcart|table|cat_supp}} | ||
| + | * '''Output''': {{vbzcart|table|_depts}} (delete/insert) | ||
==SQL== | ==SQL== | ||
| − | <section begin=sql /><mysql> | + | <section begin=sql /><mysql>CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers() |
| − | + | BEGIN | |
| − | + | DELETE FROM _depts; | |
| − | + | INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`) | |
| − | + | SELECT | |
| − | + | d.ID, | |
| − | + | d.ID_Supplier AS ID_Supp, | |
| − | + | UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey, | |
| − | + | UPPER(CONCAT_WS("-",s.CatKey,d.CatKey)) AS CatNum, | |
| − | + | LOWER(CONCAT_WS("/",s.CatKey,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept, | |
| − | + | LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title | |
| − | + | FROM cat_depts AS d LEFT JOIN cat_supp AS s ON d.ID_Supplier=s.ID; | |
| − | + | END</mysql> | |
<section end=sql /> | <section end=sql /> | ||
Revision as of 21:20, 8 March 2009
About
- Purpose: fills _depts (does not set inPrint/inStock fields)
- Input: cat_depts, cat_supp
- Output: _depts (delete/insert)
SQL
<mysql>CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers()
BEGIN
DELETE FROM _depts;
INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`)
SELECT
d.ID,
d.ID_Supplier AS ID_Supp,
UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey,
UPPER(CONCAT_WS("-",s.CatKey,d.CatKey)) AS CatNum,
LOWER(CONCAT_WS("/",s.CatKey,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept,
LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title
FROM cat_depts AS d LEFT JOIN cat_supp AS s ON d.ID_Supplier=s.ID;
END</mysql>