UPDATE orders JOIN orderdetails ON orders.orderid = orderdetails.orderid SET orderstatus = 'Ready to Ship' WHERE orders.orderstatus NOT IN ('Pending','Fraud Alert') AND orderdetails.qtyonpackingslip = 999;
UPDATE orders SET batchnumber = (SELECT * FROM (SELECT batchnumber + 1 FROM orders ORDER BY batchnumber DESC LIMIT 1) SQ) WHERE orders.orderstatus = 'Ready to Ship';
UPDATE orderdetails JOIN orders ON orders.orderid = orderdetails.orderid SET qtyonpackingslip = 0 WHERE orderdetails.qtyonhold <> 0 AND orders.orderstatus = 'Ready to Ship';
UPDATE orderdetails SET qtyonpackingslip = 0 WHERE qtyonpackingslip = 998;
UPDATE orders JOIN orderdetails ON orders.orderid = orderdetails.orderid SET order_comments = 'We are partially shipping your order and will ship the balance at our expense as soon as it arrives. We apologize for the inconvenience.' WHERE qtyonpackingslip  = 0 AND orders.orderstatus = 'Ready to Ship' AND orderdetails.productcode NOT LIKE 'DSC%'; 
UPDATE orderdetails JOIN orders ON orders.orderid = orderdetails.orderid   SET qtyonpackingslip = NULL WHERE qtyonpackingslip = 999;
UPDATE orders SET batchnumber = (SELECT batchnumber ORDER BY batchnumber DESC LIMIT 1) +1 WHERE orders.orderstatus = 'Ready to Ship' AND orderid IN (SELECT orderid FROM (SELECT od.orderid, COUNT(od.orderdetailid) AS NumLines FROM orderdetails od LEFT OUTER JOIN orders o ON od.orderid = o.orderid WHERE o.batchnumber IN(SELECT batchnumber ORDER BY batchnumber DESC) AND od.productcode NOT LIKE ('DSC-%') AND od.qtyonpackingslip IS NULL GROUP BY od.orderid) AS Count WHERE Count.NumLines >= 1);