更新關聯資料初始化

13122826258發表於2019-11-22
    --標貼表增加庫存id 
 ALTER TABLE `labels_list` ADD COLUMN `stock_id` int(11) NOT NULL COMMENT '庫存id' AFTER   `create_id`;

     --  裝箱單庫存id     
  ALTER TABLE `packing_list` ADD COLUMN `stock_id` int(11) NOT NULL COMMENT '庫存id'       AFTER`create_id`;

     -- 裝箱單刷庫存id 
    UPDATE packing_list AS l 
     LEFT JOIN ( SELECT
     m.stock_id,
      l.id 
      FROM  outbound_list_product_map AS m LEFT JOIN stock_info AS s ON m.stock_id = s.id LEFT JOIN packing_list_map AS p ON p.outbound_list_id = m.outbound_list_id LEFT JOIN packing_list AS l ON p.id = l.packing_map_id 
      WHERE  l.id IS NOT NULL AND s.model_id = l.model_id
    ) AS b ON b.id = l.id 
    SET l.stock_id = b.stock_id 
    WHERE  l.id = b.id 
    -- 出庫單標貼刷庫存id 
    UPDATE labels_list AS l LEFT JOIN ( SELECT  m.stock_id,
      l.id FROM  outbound_list_product_map AS m LEFT JOIN stock_info AS s ON m.stock_id = s.id LEFT JOIN labels_list_map AS p ON p.outbound_list_id = m.outbound_list_id LEFT JOIN labels_list AS l ON l.label_id = p.id 
      WHERE  l.id IS NOT NULL AND p.type = 1  AND s.model_id = l.model_id
    ) AS b ON b.id = l.id 
    SET l.stock_id = b.stock_id
    WHERE  l.id = b.id 
    -- 入庫單標貼刷庫存id 

UPDATE labels_list AS l 
LEFT JOIN ( SELECT  m.stock_id,
  l.id FROM  inbound_list_product_map AS m LEFT JOIN stock_info AS s ON m.stock_id = s.id LEFT JOIN labels_list_map AS p ON p.outbound_list_id = m.inbound_list_id LEFT JOIN labels_list AS l ON l.label_id = p.id 
  WHERE  l.id IS NOT NULL AND p.type = 2  AND s.model_id = l.model_id
) AS b
ON b.id = l.id 
SET l.stock_id = b.stock_id WHERE  l.id = b.id
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章