Avatar of Juan Ocasio
Juan OcasioFlag for United States of America

asked on 

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?

User generated image
User generated image
User generated imageMany thanks!

Juan
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Pawan Kumar
SOLUTION
Avatar of Panagiotis Toumpaniaris
Panagiotis Toumpaniaris
Flag of Greece image

Blurred text
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.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Blurred text
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.
Avatar of Juan Ocasio
Juan Ocasio
Flag of United States of America image

ASKER

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.
Avatar of Juan Ocasio
Juan Ocasio
Flag of United States of America image

ASKER

Icohan:  In your select statement, you're not joining the tables.  Is this correct?
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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

Avatar of lcohan
lcohan
Flag of Canada image

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/
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Blurred text
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.
Avatar of Juan Ocasio
Juan Ocasio
Flag of United States of America image

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!
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Welcome. Happy to help as always. :)
Microsoft SQL Server
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo