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

Answer 1

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.