How to use inner join in sql server to join two tables and overwrite that in first table

Question

INNER JOIN Sales_Category c1 ON c1.MPCID = c1.Category_Id 
WHERE Date_of_Purchase > DATEADD(d, -90, getdate()) 
ORDER BY c1.MPCID

Table1-OrderDetails contains fields=Bill_To_Id,MPC,Order_value,Category_Id

Table2-Sales_Category contains fields = `MPCID

What have you tried, why didn't it work? Sample data and expected results, along with your attempt(s) help us help you as well. — Thom A, Apr 07 '20 at 13:12
I get only column name with no data,sorry for mentioning as fields,I have add my query above — kavi, Apr 07 '20 at 13:13
So that's why it didn't work, now we just need everything else. — Thom A, Apr 07 '20 at 13:15
We still need *"What have you tried ... Sample data and expected results, along with your attempt(s) help us help you"* — Thom A, Apr 07 '20 at 13:17
You need to provide the full table structure along with sample data. For instance, you are referencing **Date_of_Purchase** in the where clause, but do not mention what table it is coming from. What other details are you leaving out? — Isaac, Apr 07 '20 at 13:18
I have attached the Table 1 data,Can you pls rewrite the query this is wt I need to fetch from table1 c.Bill_To_Id,c.MPC,c.Order_value with condition Date_of_Purchase > DATEADD(d, -90, getdate()) and for the MPC from table1 i should get corresponding CategoyId from Table2 and overwrite that values inTable1 — kavi, Apr 07 '20 at 13:25
What do you mean by overwrite? Do you just want to show the CategoryID from Table2 instead of Table1 in your results? Or do actually want to update the CategoryID in Table1 with the corresponding CategoryID in Table2? It is unclear because when you say overwrite there is no need to display other unrelated columns such as **MPC**. — Isaac, Apr 07 '20 at 14:01
I need to update categoryId in TABLE 1 which was fetched from TABLE2 by storing it in temp var — kavi, Apr 07 '20 at 14:40
Please read this to learn how to ask a question. https://stackoverflow.com/help/how-to-ask — Eric, Apr 07 '20 at 17:49

score 0 · Accepted Answer · answered Apr 07 '20 at 17:34

I am still not clear on if you want the Category_Id in your select query results to come from Sales_Category rather than OrderDetails or if you actually want to update the data in OrderDetails with the corresponding value in Sales_Category. Therefore, I will provide both and you can take it from there. I will warn you to be careful when running update statements because you are actually altering the data.

create table OrderDetails
(
  OrderLine_Id int
, OrderId int
, Bill_to_Id int
, Date_of_Purchase date
, MPC int
, Order_Value int
, Category_Id int
)

create table Sales_Category
(
  Category_Id int
, MPC int
)

insert into OrderDetails values
  (4, 711, 8566, '2018-05-11', 5450, 10000, null)
, (8, 5555, 8123, '2020-03-11', 6700, 20000, null)
, (103, 456, 123456, '2019-02-05', 71883, 30000, null)
, (1, 123, 67999, '2020-02-08', 7899, 40000, null)
, (2, 678, 9913, '2020-01-11', 9908, 50000, null)
, (3, 488, 98564, '2020-02-14', 999, 60000, null)
, (null, null, null, null, null, null, null)

insert into Sales_Category values
  (1, 5450)
, (2, 6700)
, (3, 9908)

-- OrderDetails populated with sample data
select * from OrderDetails

-- Sales_Category populated with sample data
select * from Sales_Category

-- this will get Category_Id from Sales_Category rather than OrderDetails
select 
  od.Bill_to_Id
, od.MPC
, od.Order_Value
, sc.Category_Id
from OrderDetails od
left join Sales_Category sc on od.MPC = sc.MPC
where od.Date_of_Purchase > DATEADD(d, -90, getdate())

-- this shows nothing has actually been updated yet
select * from OrderDetails

update OrderDetails
set Category_Id = sc.Category_Id
from OrderDetails od
inner join Sales_Category sc on od.MPC = sc.MPC
where od.Date_of_Purchase > DATEADD(d, -90, getdate())

-- this shows that Category_Id in OrderDetails has been updated where the
-- MPC values match and the Date_of_Purchase is within the last 90 days
select * from OrderDetails

Here is the demo of this code. This is the type of thing you should provide when asking a question to make is as easy as possible for someone to help you.

How to use inner join in sql server to join two tables and overwrite that in first table

1 Answers1