Difference between revisions of "VbzCart/pieces/catalog/building"
Jump to navigation
Jump to search
m (post-import tweaks) |
(2 queries; more to go) |
||
| Line 11: | Line 11: | ||
# Update all data in cat_items from data stored in [calc Items Updates saved] | # Update all data in cat_items from data stored in [calc Items Updates saved] | ||
# In cat_items, clear all isForSale flags ''except'' for items either in stock or found in [calc Items Updates saved] | # In cat_items, clear all isForSale flags ''except'' for items either in stock or found in [calc Items Updates saved] | ||
| + | ==SQL== | ||
| + | ===qryVb_Items_updates_new=== | ||
| + | <sql>SELECT c.* | ||
| + | FROM qryVb_Items_Updates AS c | ||
| + | LEFT JOIN [calc Item Updates saved] AS s | ||
| + | ON c.IDS_Update=s.IDS_Update | ||
| + | WHERE s.IDS_Update IS NULL;</sql> | ||
| + | ===qryVb_Items_updates_append=== | ||
| + | <sql>INSERT INTO [calc Item Updates saved] ( | ||
| + | ID_Item, | ||
| + | IDS_Update, | ||
| + | ID_Title, | ||
| + | ID_TGroup, | ||
| + | ID_TGTitle, | ||
| + | ID_TGItem, | ||
| + | ID_ItTyp, | ||
| + | ID_ItOpt ) | ||
| + | SELECT | ||
| + | NULL, | ||
| + | IDS_Update, | ||
| + | ID_Title, | ||
| + | ID_TGroup, | ||
| + | ID_TGTitle, | ||
| + | ID_TGItem, | ||
| + | ID_ItTyp, | ||
| + | ID_ItOpt | ||
| + | FROM qryVb_Items_Updates_new;</sql> | ||
Revision as of 14:01, 22 August 2007
Version
This is being revised yet again for the 2007 VbzCart-MySQL migration project; the previous version is here: VbzCart catalog building 2006.
Process
This is only a draft, as the process is still being worked out.
- Update the list of all catalog-based items ([calc Items Updates saved])
- Generate a list of all the items currently available from suppliers (query)
- Add any new items to [calc Items Updates saved]
- For all items in [calc Items Updates saved] where ID_Item IS NULL, create new records in cat_items and update the corresponding records in [calc Items Updates saved]
- Update all data in cat_items from data stored in [calc Items Updates saved]
- In cat_items, clear all isForSale flags except for items either in stock or found in [calc Items Updates saved]
SQL
qryVb_Items_updates_new
<sql>SELECT c.*
FROM qryVb_Items_Updates AS c LEFT JOIN [calc Item Updates saved] AS s ON c.IDS_Update=s.IDS_Update WHERE s.IDS_Update IS NULL;</sql>
qryVb_Items_updates_append
<sql>INSERT INTO [calc Item Updates saved] (
ID_Item, IDS_Update, ID_Title, ID_TGroup, ID_TGTitle, ID_TGItem, ID_ItTyp, ID_ItOpt )
SELECT
NULL, IDS_Update, ID_Title, ID_TGroup, ID_TGTitle, ID_TGItem, ID_ItTyp, ID_ItOpt
FROM qryVb_Items_Updates_new;</sql>