VbzCart/procs/Upd Depts fr Depts Suppliers

From HTYP, the free directory anyone can edit

[edit] About

• Purpose: fills _depts (does not set inPrint/inStock fields)
• Input: cat_depts, cat_supp
• Output: _depts (delete/insert)

[edit] SQL

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