title: REFUND MySQL query
body: <p>I currently have 5 tables in my DB</p>
  
  <p>branch, customers, orderline, orders, products</p>
  
  <p>I want to be able to refund an order through its orderid 
  refund meaning: 
  setting the orders total price back to 0.00,
  each orderlines total price to 0.00,
  adding the quantitys back from the orderline to the products,
  aswell as having the orders orderstatus to RFND </p>
  
  <p>heres my database so far</p>
  
  <pre><code>DROP DATABASE IF EXISTS MMWally;
  
  CREATE DATABASE IF NOT EXISTS MMWally;
  
  USE MMWally;
  
  --
  -- Definition of table `categories`
  --
  
  DROP TABLE IF EXISTS `products`;
  CREATE TABLE `products`
  (
      `ProductID` int NOT NULL,
      `ProductName` varchar(50) NOT NULL, 
      `wPrice` double(13, 2) NOT NULL,
      `Stock` int NOT NULL,
      `BranchID` int NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  
  DROP TABLE IF EXISTS `customers`;
  CREATE TABLE `customers` (
    `CustomerID` int NOT NULL,
    `FirstName` tinytext NOT NULL,
    `LastName` tinytext NOT NULL,
    `Phone` VARCHAR(10) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  
  DROP TABLE IF EXISTS `branch`;
  CREATE TABLE `branch`
  (
      `BranchID` int NOT NULL,
      `BranchName` tinytext DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  
  DROP TABLE IF EXISTS `orders`;
  CREATE TABLE `orders`
  (
      `OrderID` int NOT NULL,
      `CustomerID` int NOT NULL,
      `BranchID` int NOT NULL,
      `OrderDate` date NOT NULL,
      `sPrice` double(13, 2) DEFAULT NULL,
      `Status` tinytext NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  DROP TABLE IF EXISTS `orderline`;
  CREATE TABLE `orderline`
  (
      `OrderID` int NOT NULL,
      `OrderLineID` int NOT NULL,
      `ProductID` int NOT NULL,
      `ItemQuantity` int NOT NULL,
      `TotalPrice` double(13, 2) 
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  
  ALTER TABLE orders MODIFY OrderID INT AUTO_INCREMENT PRIMARY KEY; 
  ALTER TABLE orderline MODIFY OrderLineID INT AUTO_INCREMENT PRIMARY KEY; 
  ALTER TABLE branch MODIFY BranchID INT AUTO_INCREMENT PRIMARY KEY;
  ALTER TABLE customers MODIFY CustomerID INT AUTO_INCREMENT PRIMARY KEY;
  ALTER TABLE products MODIFY ProductID INT AUTO_INCREMENT PRIMARY KEY;
  
  
  /* FOREIGN KEYS */
  
  ALTER TABLE orders ADD CONSTRAINT FOREIGN KEY (CustomerID) REFERENCES customers (CustomerID);
  ALTER TABLE orders ADD CONSTRAINT FOREIGN KEY (BranchID) REFERENCES branch (BranchID);
  ALTER TABLE products ADD CONSTRAINT FOREIGN KEY (BranchID) REFERENCES branch (BranchID);
  ALTER TABLE orderline ADD CONSTRAINT FOREIGN KEY (OrderID) REFERENCES orders (OrderID);
  ALTER TABLE orderline ADD CONSTRAINT FOREIGN KEY (ProductID) REFERENCES products (ProductID);
  
  ALTER TABLE orders ADD CONSTRAINT CHECK (`Status` = 'PAID' OR `Status` = 'RFND');
  
  /*!40000 ALTER TABLE `branch` DISABLE KEYS */;
  INSERT INTO `branch` (`BranchID`, `BranchName`) VALUES 
   (1, 'Sports World'),
   (2, 'Waterloo'),
   (3, 'Cambridge Mall');
  /*!40000 ALTER TABLE `branch` ENABLE KEYS */;
  
  /*!40000 ALTER TABLE `customers` DISABLE KEYS */;
  INSERT INTO `customers` (`CustomerID`, `FirstName`, `LastName`, `Phone`) VALUES 
   (1, 'Carlo', 'Sgro', 5195550000),
   (2, 'Norbert', 'Mika', 4165551111),
   (3, 'Russell', 'Foubert', 5195552222),
   (4, 'Sean', 'Clarke', 5195553333),
   (5, 'Sean', 'Clarke', 5192395285);
  /*!40000 ALTER TABLE `customers` ENABLE KEYS */;
  
  /*!40000 ALTER TABLE `products` DISABLE KEYS */;
  INSERT INTO `products` (`ProductID`,`ProductName`, `wPrice`, `Stock`, `BranchID`) VALUES 
   (1, 'Disco Queen Wallpaper (roll)', 12.95, 56, 1),
   (2, 'Countryside Wallpaper (roll)', 11.95, 24, 1),
   (3, 'Drywall Tape (roll)', 3.95, 120, 2),
   (4, 'Drywall Tape (pkg 10)', 36.95, 30, 2),
   (5, 'Drywall Repair Compound (tube)', 6.95, 90, 3),
   (6, 'Victorian Lace Wallpaper (roll)', 14.95, 44, 3);
  /*!40000 ALTER TABLE `products` ENABLE KEYS */;
  
  
  /*!40000 ALTER TABLE `orders` DISABLE KEYS */;
  INSERT INTO orders(CustomerID, BranchID, OrderDate, sPrice, Status) VALUES 
  (1, 1, '2019-07-20', 20.93, 'PAID'),
  (2, 1, '2019-07-20', 9.73, 'PAID'),
  (3, 1, '2019-07-20', 5.53, 'PAID'),
  (4, 3, '2019-10-06', 16.73, 'PAID'),
  (4, 2, '2019-11-02', 18.13, 'PAID'),
  (3, 2, '2019-11-02', 5.53, 'PAID'),
  (2, 2, '2019-11-02', 18.13, 'RFND'),
  (1, 2, '2019-11-02', 5.53, 'RFND');
  /*!40000 ALTER TABLE `orders` ENABLE KEYS */;
  </code></pre>