Skip to content

Instantly share code, notes, and snippets.

@svschannak

svschannak/update_pickware.sql

Created June 14, 2019 12:44
Show Gist options
  • Save svschannak/a25bae9bd432b4b1f2e90664313062a2 to your computer and use it in GitHub Desktop.
Save svschannak/a25bae9bd432b4b1f2e90664313062a2 to your computer and use it in GitHub Desktop.
Download ZIP
update_pickware.sql
Raw
update_pickware.sql
UPDATE db_web1_1.pickware_erp_warehouse_article_detail_configurations pew
JOIN (SELECT sod.name name,
pew.articleDetailId detailID,
pew.minimumStock minimumStock,
SUM(sod.quantity) sum_quantity
FROM db_web1_1.s_order_details sod
JOIN db_web1_1.s_order so ON sod.orderID = so.id
JOIN db_web1_1.s_articles_details sad ON sod.articleordernumber = sad.ordernumber
JOIN db_web1_1.s_articles_attributes saa ON sad.id = saa.articledetailsID
JOIN db_web1_1.pickware_erp_warehouse_article_detail_configurations pew ON sad.id=pew.articleDetailId
WHERE (so.ordertime >= NOW() - INTERVAL (saa.attr11) DAY) && pew.warehouseId = 1
GROUP BY detailID) tmp on pew.articleDetailId = tmp.detailID
SET pew.targetStock = tmp.sum_quantity
WHERE pew.warehouseId = 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment