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.
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?
Many thanks!
Juan
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?
Many thanks!Juan
Asked:
3-
3
2- +1
3 Solutions
--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'
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'
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??
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.
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.
Please try this -
Take backup of Orders Table first.
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]
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/
"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/
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
SELECT Query
--Update Command
--After Update
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
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)
--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'
--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)
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!
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!
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 Already a member? Login.
Featured Post
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.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Open in new window
Something like that?