• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 55
  • Last Modified:

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
0
Juan Ocasio
Asked:
Juan Ocasio
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
Panagiotis ToumpaniarisSystem EngineerCommented:
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?
0
 
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??
1
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Juan OcasioApplication DeveloperAuthor Commented:
Icohan:  In your select statement, you're not joining the tables.  Is this correct?
0
 
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

0
 
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/
0
 
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

0
 
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!
0
 
Pawan KumarDatabase ExpertCommented:
Welcome. Happy to help as always. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now