I have two tables (shown below) Orders and OrdersUpdate. What I am trying to do is update the orders table with the new order numbers located in the OrdersUpdate table. What I have to do is join them on three fields, Order, M1, and M2, and have a date range between 10/15/2017 and 10/21/2017. I've tried using Inner Join joining on the OrdersUpdate Table three times (just selecting to see if data is retrieved -
SELECT * FROM Orders o
INNER JOIN OrdersUpdate ou1 ON o.Order = ou1.Order
INNER JOIN OrdersUpdate ou2 ON o.M1 = ou2.M1
INNER JOIN OrdersUpdate ou3 ON o.M2 = ou2.M2
WHERE o.Date BETWEEN '10/15/2017' AND '10/21/2017'
but that is not returning anything, and I know there is data there.
Can someone please assist in helping me correctly join these tables so that I can update the old order numbers with the new order numbers?