Updating SQL Server 2010 table using another table

Hello All:

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?

Orders Table
OrdersUpdate Table
After UpdateMany thanks!

Juan
LVL 15
Juan OcasioApplication DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Panagiotis ToumpaniarisSystem ArchitectCommented: 
UPDATE
    Orders
SET
    Orders.Order = OrdersUpdate.Order
FROM
   Orders Ord
INNER JOIN
    OrdersUpdate Ordu
ON 
    Ord.M1= Ordu.M1
AND
    Ord.M2 = Ordu.M2;

Open in new window


Something like that?
lcohanDatabase AnalystCommented: 
--this is to check what will be updated
select Orders.[Order] as OldOrder, OrdersUpdate.NewOrderNo as NewOrder
from OrdersUpdate 
where Orders.[Order] = OrdersUpdate.[Order] -- essentially this is enough 
	and	Orders.M1 = OrdersUpdate.M1 
	and	Orders.M2 = OrdersUpdate.M2 
	and	Orders.[Date] BETWEEN '10/15/2017' AND '10/21/2017'

Open in new window


so the update would look like:

update Orders set [Order] = OrdersUpdate.NewOrderNo
from OrdersUpdate 
where Orders.[Order] = OrdersUpdate.[Order] -- essentially this is enough 
	and	Orders.M1 = OrdersUpdate.M1 
	and	Orders.M2 = OrdersUpdate.M2 
	and	Orders.[Date] BETWEEN '10/15/2017' AND '10/21/2017'

Open in new window


however... in my opinion - extreme CAUTION with the above update!!!
from business app point of view, you should at least log/audit what was changed as in my opinion is never OK the change an original order number as you will never ever be able to refer back to it once it has been changed right? What about OrderItems and any other related tables to that original Order ID??
Juan OcasioApplication DeveloperAuthor Commented:
Thanks all:

lcohan: I agree.  My scenario is a bit contrived as I wanted to get the intent across without using actual tables/data.  I would need to use more than just the order as there are multiple M1s and M2s attached to each order and I have to update the correct one with the new order number (see order 12345 above).

Many thanks to both of you.  I will give this a shot and report back.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
Juan OcasioApplication DeveloperAuthor Commented:
Icohan:  In your select statement, you're not joining the tables.  Is this correct?
Pawan KumarDatabase ExpertCommented:
Please try this -

Take backup of Orders Table first.

Update x
SET
  x.Order = y.NewOrderNo
FROM Orders o
INNER JOIN OrdersUpdate y 
ON o.Order = y.Order
AND o.M1 = y.M1
AND o.M2 = y.M2
AND o.[Date] = y.[Date]

Open in new window

lcohanDatabase AnalystCommented:
In this case as we have INNER JOIN vs. WHERE ON I think even the query plans would be built identical however:
"They are not semantically equivalent even if sometimes they both produce the same outcome. The ON clause defines the relationship between the tables. The WHERE clause describes which rows you are interested in. WHERE Clause only supports for Inner join not for Outer joins like LEFT JOIN and RIGHT JOIN ."
So generaly speaking:  "Put filter expression on WHERE clause;; Put only joining expression on JOIN . That is, only the relationship between the tables goes here."

More here: https://blog.sqlauthority.com/2014/10/13/sql-server-what-is-the-difference-between-an-inner-join-and-where-clause/
Pawan KumarDatabase ExpertCommented:
Please try full and tested solution just for you.

It matches exact output you need and I have also given you the select query to get data.

Generate Sample data

CREATE TABLE Orders
(
	 [Order] BIGINT
	,M1 BIGINT 
	,M2 BIGINT
	,[Date] Date 
	,Item Varchar(10)
)
GO

CREATE TABLE OrdersUpdate
(
	 [Order] BIGINT
	,M1 BIGINT 
	,M2 BIGINT
	,NewOrderNo BIGINT
)
GO

INSERT INTO OrdersUpdate VALUES
(12345,55555,121212,21215),
(12345,11111,44444,98652),
(54321,77777,77777,89653),
(54321,66666,22222,12352),
(23456,33333,55555,52424)
GO

INSERT INTO Orders VALUES
(12345,55555,121212,'10/15/2017','Item'),
(12345,55555,121212,'10/15/2017','Bread'),
(12345,55555,121212,'10/15/2017','Cereal'),
(12345,11111,44444,'10/17/2017','Eggs'),
(12345,11111,44444,'10/17/2017','Milk'),
(54321,77777,77777,'10/21/2017','Sausage'),
(54321,66666,22222,'10/16/2017','Bacon'),
(23456,33333,55555,'10/19/2017','Juice')
GO

Open in new window



SELECT Query 

SELECT O.[Order],oU1.NewOrderNo, O.M1,O.M2,O.Item,O.[Date] FROM Orders o 
INNER JOIN OrdersUpdate ou1 ON o.[Order] = ou1.[Order]
and o.M1 = ou1.M1
aND o.M2 = ou1.M2
WHERE o.Date BETWEEN '10/15/2017' AND '10/21/2017'

/*------------------------
output
------------------------*/
Order                NewOrderNo           M1                   M2                   Item       Date
-------------------- -------------------- -------------------- -------------------- ---------- ----------
12345                21215                55555                121212               Item       2017-10-15
12345                21215                55555                121212               Bread      2017-10-15
12345                21215                55555                121212               Cereal     2017-10-15
12345                98652                11111                44444                Eggs       2017-10-17
12345                98652                11111                44444                Milk       2017-10-17
54321                89653                77777                77777                Sausage    2017-10-21
54321                12352                66666                22222                Bacon      2017-10-16
23456                52424                33333                55555                Juice      2017-10-19

(8 row(s) affected)

Open in new window



--Update Command 

UPDATE o 
	SET o.[Order] = ou1.NewOrderNo
FROM Orders o 
INNER JOIN OrdersUpdate ou1 ON o.[Order] = ou1.[Order]
and o.M1 = ou1.M1
aND o.M2 = ou1.M2
WHERE o.Date BETWEEN '10/15/2017' AND '10/21/2017'

Open in new window



--After Update 

/*------------------------
SELECT * FROM Orders
------------------------*/
Order                M1                   M2                   Date       Item
-------------------- -------------------- -------------------- ---------- ----------
21215                55555                121212               2017-10-15 Item
21215                55555                121212               2017-10-15 Bread
21215                55555                121212               2017-10-15 Cereal
98652                11111                44444                2017-10-17 Eggs
98652                11111                44444                2017-10-17 Milk
89653                77777                77777                2017-10-21 Sausage
12352                66666                22222                2017-10-16 Bacon
52424                33333                55555                2017-10-19 Juice

(8 row(s) affected)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan OcasioApplication DeveloperAuthor Commented:
Thanks all:

Appreciate the help.  Looking at my original with the INNER JOINS, that also worked.  It looks like I may have some malformed data that was provided.  The suggestions here also worked, so I am awarding points accordingly.

Thanks again!
Pawan KumarDatabase ExpertCommented:
Welcome. Happy to help as always. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.