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
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Icohan: In your select statement, you're not joining the tables. Is this correct?
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/
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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!
Welcome. Happy to help as always. :)
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
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.